DATEPART function page 2
In the MySQL dialect, each date component can be extracted from a date value with the correspondent function. Suppose we must query the Airport database to get minutes of department time of the flights that departs between 1 and 2 p.m.
Time components can also be got with the EXTRACT function. In this case the solution can be written in the following form:
The EXTRACT function can also return composite components of date and/or time, for example, year and month. Let's solve the following exercise.
Query the Paintings database to count the number of paintings in months of each year.
The full list of possible components can be found, for example,
It is appropriate to mention here rather free style of MySQL grouping. Columns of a SELECT clause are allowed not to be present in the GROUP BY column list. It is obvious that an implicit aggregate function is supposed to be there, otherwise it would be impossible to interpret the statement unambiguously. I suppose that MIN or MAX functions are used implicitly but I'm not going to make it out here, because this feature contradicts SQL standard.
As far as I know, there are no functions like YEAR, MONTH, etc. in PostgreSQL. Still the EXTRACT function exists, and the second solution variant that we've written for MySQL is valid for this DBMS too:
There is yet a function analogous to the DATEPART of MS SQL. A syntactical difference between them may be demonstrated with the same exercise solution:
proceed to the second exercise. Compound components are not supported
by the PostgreSQL EXTRACT function, therefore an ordinary grouping by
two components, year and month, may be used:
Nevertheless, a compound time component can be imitated with the
The produced result will be analogous to the one of Solution 3 except the first two columns, which, of course, can be added to the output by including their names in the GROUP BY clause like in Solution 4.