How to Extract a Date Part in SQL

Posted on by lukaseder
The Modern SQL Twitter account (by Markus Winand) published a hint about how to extract a date part in SQL:
Is it true? Yes it is, in the SQL standard and in a variety of standards-compliant databases. But let’s check what jOOQ does when you run the following program on all 18 currently supported RDBMS: 

import static org.jooq.impl.DSL.currentDate;
import static org.jooq.impl.DSL.extract;
import static org.jooq.impl.DSL.using;

import java.util.stream.Stream;

import org.jooq.DatePart;
import org.jooq.SQLDialect;

public class Extract {
    public static void main(String[] args) {
        // Get all distinct SQLDialect families
        Stream
        .of(SQLDialect.values())
        .map(SQLDialect::family)
        .distinct()
        .forEach(family -> {
            System.out.println();
            System.out.println(family);

            // Get all supported date parts
            Stream
            .of(DatePart.values())

            // For each family / part, get the
            // EXTRACT() function
            .map(part -> extract(currentDate(), part))
            .forEach(expr -> {
                System.out.println(
                    using(family).render(expr)
                );
            });
        });
    }
}
The output is: Open Source databases 
DEFAULT
extract(year from current_date())
extract(month from current_date())
extract(day from current_date())
extract(hour from current_date())
extract(minute from current_date())
extract(second from current_date())

CUBRID
extract(year from current_date())
extract(month from current_date())
extract(day from current_date())
extract(hour from current_date())
extract(minute from current_date())
extract(second from current_date())

DERBY
year(current_date)
month(current_date)
day(current_date)
hour(current_date)
minute(current_date)
second(current_date)

FIREBIRD
extract(year from current_date)
extract(month from current_date)
extract(day from current_date)
extract(hour from current_date)
extract(minute from current_date)
extract(second from current_date)

H2
extract(year from current_date())
extract(month from current_date())
extract(day from current_date())
extract(hour from current_date())
extract(minute from current_date())
extract(second from current_date())

HSQLDB
extract(year from current_date)
extract(month from current_date)
extract(day from current_date)
extract(hour from current_date)
extract(minute from current_date)
extract(second from current_date)

MARIADB
extract(year from current_date())
extract(month from current_date())
extract(day from current_date())
extract(hour from current_date())
extract(minute from current_date())
extract(second from current_date())

MYSQL
extract(year from current_date())
extract(month from current_date())
extract(day from current_date())
extract(hour from current_date())
extract(minute from current_date())
extract(second from current_date())

POSTGRES
extract(year from current_date)
extract(month from current_date)
extract(day from current_date)
extract(hour from current_date)
extract(minute from current_date)
extract(second from current_date)

SQLITE
strftime('%Y', current_date)
strftime('%m', current_date)
strftime('%d', current_date)
strftime('%H', current_date)
strftime('%M', current_date)
strftime('%S', current_date)
Commercial databases 
ACCESS
datepart('yyyy', date())
datepart('m', date())
datepart('d', date())
datepart('h', date())
datepart('n', date())
datepart('s', date())

ASE
datepart(yy, current_date())
datepart(mm, current_date())
datepart(dd, current_date())
datepart(hh, current_date())
datepart(mi, current_date())
datepart(ss, current_date())

DB2
year(current_date)
month(current_date)
day(current_date)
hour(current_date)
minute(current_date)
second(current_date)

HANA
extract(year from current_date)
extract(month from current_date)
extract(day from current_date)
extract(hour from current_date)
extract(minute from current_date)
extract(second from current_date)

INFORMIX
year(current year to day)
month(current year to day)
day(current year to day)
current year to day::datetime hour to hour::char(2)::int
current year to day::datetime minute to minute::char(2)::int
current year to day::datetime second to second::char(2)::int

INGRES
extract(year from current_date)
extract(month from current_date)
extract(day from current_date)
extract(hour from current_date)
extract(minute from current_date)
extract(second from current_date)

ORACLE (in jOOQ 3.5)
to_char(trunc(sysdate), 'YYYY')
to_char(trunc(sysdate), 'MM')
to_char(trunc(sysdate), 'DD')
to_char(trunc(sysdate), 'HH24')
to_char(trunc(sysdate), 'MI')
to_char(trunc(sysdate), 'SS')

ORACLE (in jOOQ 3.6)
extract(year from current_date)
extract(month from current_date)
extract(day from current_date)
extract(hour from cast(current_date as timestamp))
extract(minute from cast(current_date as timestamp))
extract(second from cast(current_date as timestamp))

SQLSERVER
datepart(yy, convert(date, current_timestamp))
datepart(mm, convert(date, current_timestamp))
datepart(dd, convert(date, current_timestamp))
datepart(hh, convert(date, current_timestamp))
datepart(mi, convert(date, current_timestamp))
datepart(ss, convert(date, current_timestamp))

SYBASE
datepart(yy, current date)
datepart(mm, current date)
datepart(dd, current date)
datepart(hh, current date)
datepart(mi, current date)
datepart(ss, current date)
Yes. The standard… If only it were implemented thoroughly…

Published by lukaseder

I made jOOQ

Leave a Reply