DATEPART function

Syntax:

DATEPART(datepart , date)

This function returns an integer representing the specified datepart of the specified date.

The above list of available values of datepart argument is added by the following

Note that the value returned by the DATEPART function in this case (weekday) depends on the value set by SET DATEFIRST parameter, which sets the first day of the week. Default value is Sunday = 1 if language setting is English.

Let us turn back to above example. Under suggestions that the departure/landing times are measured with an accuracy of one minute, we can define duration of flight in minutes as minimal integer units stored. So, departure time of the trip no.1123 in minutes is

SELECT DATEPART(hh, time_out)*60 + DATEPART(mi, time_out)
FROM Trip
WHERE trip_no = 1123;

SELECT DATEPART(hh, time_in)*60 + DATEPART(mi, time_in)
FROM Trip
WHERE trip_no = 1123;

Now we need to compare whether the landing time exceeds the departure time. If so, we must subtract the second time value from the first time value; otherwise, 1440 minutes (one day = 60*24) need to be added to the remainder.

SELECT CASE
WHEN time_dep >= time_arr
THEN time_arr - time_dep + 1440
ELSE time_arr - time_dep
END dur
FROM (SELECT DATEPART(hh, time_out)*60 + DATEPART(mi, time_out) time_dep,
DATEPART(hh, time_in)*60 + DATEPART(mi, time_in) time_arr
FROM Trip
WHERE trip_no = 1123
) tm;

Here, we use subquery to avoid repetition of cumbersome constructions in the CASE operator. Despite of tedious form, the result is absolute correct in view of above remarks.

Example 7.1.4

Only a date but not a time is stored in the Pass_in_trip table. This is because any trip is executed only once a day. To solve this task, we need to combine the time from the Trip table with the date from the Pass_in_trip table.

SELECT DISTINCT pt.trip_no, DATEADD(mi, DATEPART(hh,time_out)*60 +
DATEPART(mi,time_out), date) [time]
FROM Pass_in_trip pt JOIN
Trip t ON pt.trip_no = t.trip_no
WHERE t.trip_no = 1123;

If you’ll run the above query, the following result will be obtained

DISTINCT is used here to eliminate duplicates if any. It should be noted that trip number and day is duplicated in Pass_in_trip table for each passenger of the same trip.

In some cases it is possible to replace DATEPART function with more simple functions. Here they are:

DAY(date) — integer representation of day of the specified date. This function is equivalent to function DATEPART (dd, date).

MONTH(date) — integer representation of month of the specified date. This function is equivalent to function DATEPART (mm, date).

YEAR(date) — integer representation of year of the specified date. This function is equivalent to function DATEPART (yy, date).

Suggested exercises: 43, 75, 110,119,143

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.

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.