SQL Exercises news letters, latest issue

DATEPART function and MySQL & PostgreSQL

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 qty
from utb
group by EXTRACT(YEAR_MONTH FROM b_datetime);

y     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

Remark

It is appropriate to mention here rather free style of MySQL grouping. Colu9mns 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.

Read more...

Useful links

§ All articles from news letters are putting on the site Books and articles about SQL thereafter.

§ We invite you to visit the Interactive SQL textbook.
    The resource is positioned as a "handbook" for this site SQL-EX.COM, but can be used irrespective of it also.

§ The following DBMS are used on the site for solving exercises: SQL Server 2008 R2 Express, MySQL 5, PostgreSQL 9.