Transact-SQL string and datetime functions

T-SQL Datetime functions

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 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);

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

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 qty
FROM utb
GROUP 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 qty
FROM utb
GROUP 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.