07:02

DATEPART function page 2

MySQL

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.

Solution 1

`SELECT time_out, MINUTE(time_out)  FROM trip WHERE HOUR(time_out) = 12;`
time_out    min
1900-01-01 12:35:00   35
1900-01-01 12:00:00   0

Time components can also be got with the EXTRACT function. In this case the solution can be written in the following form:

Solution 2

`SELECT time_out, EXTRACT(MINUTE FROM time_out) AS MIN FROM trip WHERE EXTRACT(HOUR FROM time_out) = 12;`

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.

Solution 3

`SELECT YEAR(b_datetime) AS y, MONTH(b_datetime) AS m, EXTRACT(YEAR_MONTH FROM b_datetime) AS ym, COUNT(*) AS qtyFROM utbGROUP BY EXTRACT(YEAR_MONTH FROM b_datetime);`

m   ym     qty
2000 1 200001 1
2001 1 200101 1
2002 1 200201 1
2002 6 200206 1
2003 1 200301 69
2003 2 200302 7
2003 3 200303 2
2003 4 200304 2
2003 5 200305 2
2003 6 200306 2

The full list of possible components can be found, for example, here.

Notes:

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.

PostgreSQL

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:

`SELECT time_out, EXTRACT(MINUTE FROM time_out) AS MIN FROM trip WHERE EXTRACT(HOUR FROM time_out) = 12;`

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:

`SELECT time_out, DATE_PART('MINUTE', time_out) AS MIN FROM trip WHERE DATE_PART('HOUR', time_out) = 12;`

Let's 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:

Solution 4

`SELECT EXTRACT(YEAR FROM b_datetime) AS y, EXTRACT(MONTH FROM b_datetime) AS m, COUNT(*) AS qtyFROM utbGROUP BY EXTRACT(YEAR FROM b_datetime), EXTRACT(MONTH FROM b_datetime);`

Nevertheless, a compound time component can be imitated with the TO_CHAR formatting function:

`SELECT TO_CHAR(b_datetime, 'YYYYMM') AS ym, COUNT(*) AS qtyFROM utbGROUP BY TO_CHAR(b_datetime, 'YYYYMM');`

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.

 Pages 1 2