You’re Very Likely to Have Gotten SQL Date Time Arithmetic Wrong!


You’re very likely to have gotten SQL date time arithmetic wrong. And why is that? Google it! You’ll quickly find blog posts like these:

And they’re all advocating stuff like this:

SYSDATE + (10/1440) is ten minutes from now.

Is it really? What about this beauty:

SELECT TO_CHAR(hiredate,'DD.MM.YYYY:HH24:MI:SS') "Hiredate",
       TO_CHAR(&Today,'DD.MM.YYYY:HH24:MI:SS') "Today",
       trunc(86400*(&Today-hiredate))-60*(trunc((86400*(&&Today-hiredate))/60)) "Sec",
       trunc((86400*(&Today-hiredate))/60)-60*(trunc(((86400*(&&Today-hiredate))/60)/60)) "Min",
       trunc(((86400*(&Today-hiredate))/60)/60)-24*(trunc((((86400*(&&Today-hiredate))/60)/60)/24)) "Hrs",
       trunc((((86400*(&Today-hiredate))/60)/60)/24) "Days"
FROM emp;

Did you think about timezones? Did you think about daylight savings time? Did you think about leap seconds? And there many other things that can go horribly wrong, when you think you can beat date time arithmetic by counting seconds, days and other entities. Intead of adding things up by yourself, use vendor-specific built-in functions. Unfortunately, they’re a bit hard to remember.

But luckily, there’s jOOQ to standardise SQL. We’ve blogged about expression standardisation before, and we’re doing it again, with this simple date time arithmetic test programme:

import static org.jooq.DatePart.DAY;
import static org.jooq.DatePart.HOUR;
import static org.jooq.DatePart.MINUTE;
import static org.jooq.DatePart.MONTH;
import static org.jooq.DatePart.SECOND;
import static org.jooq.DatePart.YEAR;
import static org.jooq.SQLDialect.INGRES;
import static org.jooq.SQLDialect.SQL99;
import static org.jooq.impl.DSL.select;
import static org.jooq.impl.DSL.timestampAdd;
import static org.jooq.impl.DSL.using;

import java.sql.Timestamp;
import java.util.EnumSet;

import org.jooq.QueryPart;
import org.jooq.SQLDialect;
import org.jooq.conf.Settings;

public class Compatibility {

    public static void main(String[] args) {
        Timestamp t = new Timestamp(0);

        // Using the new version of the 
        // timestampAdd() function
        // that will be added in jOOQ 3.3
        print(select(
            timestampAdd(t, 2, YEAR)  .as("yy"),
            timestampAdd(t, 2, MONTH) .as("mm"),
            timestampAdd(t, 2, DAY)   .as("dd"),
            timestampAdd(t, 2, HOUR)  .as("hh"),
            timestampAdd(t, 2, MINUTE).as("mi"),
            timestampAdd(t, 2, SECOND).as("ss")
        ));
    }

    private static void print(QueryPart part) {
        System.out.println("Printing " + part);
        System.out.println("---------------------");

        // Get only SQLDialect families, don't
        // distinguish between
        // SQL Server 2008 or SQL Server 20012
        EnumSet<SQLDialect> dialects =
            EnumSet.noneOf(SQLDialect.class);
        for (SQLDialect dialect:SQLDialect.values())
            if (dialect != SQL99 && dialect != INGRES)
                dialects.add(dialect.family());

        // Render the passed in SQL clause to
        // all supported SQL dialects
        for (SQLDialect dialect: dialects)
            System.out.println(
                String.format("%1$s: \n%2$s\n",
                dialect, using(dialect, new Settings()
                         .withRenderFormatted(true))
                         .renderInlined(part)
            ));

        System.out.println();
        System.out.println();
    }
}

And here’s the output for most databases currently supported by jOOQ:

-- CUBRID: 
select 
  date_add(datetime '1970-01-01 01:00:00.0', interval 2 year) "yy", 
  date_add(datetime '1970-01-01 01:00:00.0', interval 2 month) "mm", 
  date_add(datetime '1970-01-01 01:00:00.0', interval 2 day) "dd", 
  date_add(datetime '1970-01-01 01:00:00.0', interval 2 hour) "hh", 
  date_add(datetime '1970-01-01 01:00:00.0', interval 2 minute) "mi", 
  date_add(datetime '1970-01-01 01:00:00.0', interval 2 second) "ss"
from "db_root"

-- DERBY: 
select 
  {fn timestampadd(sql_tsi_year, 2, timestamp('1970-01-01 01:00:00.0')) } as "yy", 
  {fn timestampadd(sql_tsi_month, 2, timestamp('1970-01-01 01:00:00.0')) } as "mm", 
  {fn timestampadd(sql_tsi_day, 2, timestamp('1970-01-01 01:00:00.0')) } as "dd", 
  {fn timestampadd(sql_tsi_hour, 2, timestamp('1970-01-01 01:00:00.0')) } as "hh", 
  {fn timestampadd(sql_tsi_minute, 2, timestamp('1970-01-01 01:00:00.0')) } as "mi", 
  {fn timestampadd(sql_tsi_second, 2, timestamp('1970-01-01 01:00:00.0')) } as "ss"
from "SYSIBM"."SYSDUMMY1"

-- FIREBIRD: 
select 
  dateadd(year, 2, timestamp '1970-01-01 01:00:00.0') "yy", 
  dateadd(month, 2, timestamp '1970-01-01 01:00:00.0') "mm", 
  dateadd(day, 2, timestamp '1970-01-01 01:00:00.0') "dd", 
  dateadd(hour, 2, timestamp '1970-01-01 01:00:00.0') "hh", 
  dateadd(minute, 2, timestamp '1970-01-01 01:00:00.0') "mi", 
  dateadd(second, 2, timestamp '1970-01-01 01:00:00.0') "ss"
from "RDB$DATABASE"

-- H2: 
select 
  dateadd('year', 2, timestamp '1970-01-01 01:00:00.0') "yy", 
  dateadd('month', 2, timestamp '1970-01-01 01:00:00.0') "mm", 
  dateadd('day', 2, timestamp '1970-01-01 01:00:00.0') "dd", 
  dateadd('hour', 2, timestamp '1970-01-01 01:00:00.0') "hh", 
  dateadd('minute', 2, timestamp '1970-01-01 01:00:00.0') "mi", 
  dateadd('second', 2, timestamp '1970-01-01 01:00:00.0') "ss"
from dual

-- HSQLDB: 
select 
  {fn timestampadd(sql_tsi_year, 2, timestamp '1970-01-01 01:00:00.0') } as "yy", 
  {fn timestampadd(sql_tsi_month, 2, timestamp '1970-01-01 01:00:00.0') } as "mm", 
  {fn timestampadd(sql_tsi_day, 2, timestamp '1970-01-01 01:00:00.0') } as "dd", 
  {fn timestampadd(sql_tsi_hour, 2, timestamp '1970-01-01 01:00:00.0') } as "hh", 
  {fn timestampadd(sql_tsi_minute, 2, timestamp '1970-01-01 01:00:00.0') } as "mi", 
  {fn timestampadd(sql_tsi_second, 2, timestamp '1970-01-01 01:00:00.0') } as "ss"
from "INFORMATION_SCHEMA"."SYSTEM_USERS"

-- MARIADB: 
select 
  date_add(timestamp '1970-01-01 01:00:00.0', interval 2 year) as `yy`, 
  date_add(timestamp '1970-01-01 01:00:00.0', interval 2 month) as `mm`, 
  date_add(timestamp '1970-01-01 01:00:00.0', interval 2 day) as `dd`, 
  date_add(timestamp '1970-01-01 01:00:00.0', interval 2 hour) as `hh`, 
  date_add(timestamp '1970-01-01 01:00:00.0', interval 2 minute) as `mi`, 
  date_add(timestamp '1970-01-01 01:00:00.0', interval 2 second) as `ss`
from dual

-- MYSQL: 
select 
  date_add(timestamp '1970-01-01 01:00:00.0', interval 2 year) as `yy`, 
  date_add(timestamp '1970-01-01 01:00:00.0', interval 2 month) as `mm`, 
  date_add(timestamp '1970-01-01 01:00:00.0', interval 2 day) as `dd`, 
  date_add(timestamp '1970-01-01 01:00:00.0', interval 2 hour) as `hh`, 
  date_add(timestamp '1970-01-01 01:00:00.0', interval 2 minute) as `mi`, 
  date_add(timestamp '1970-01-01 01:00:00.0', interval 2 second) as `ss`
from dual

-- POSTGRES: 
select 
  (timestamp '1970-01-01 01:00:00.0' + (2 || ' year')::interval) as "yy", 
  (timestamp '1970-01-01 01:00:00.0' + (2 || ' month')::interval) as "mm", 
  (timestamp '1970-01-01 01:00:00.0' + (2 || ' day')::interval) as "dd", 
  (timestamp '1970-01-01 01:00:00.0' + (2 || ' hour')::interval) as "hh", 
  (timestamp '1970-01-01 01:00:00.0' + (2 || ' minute')::interval) as "mi", 
  (timestamp '1970-01-01 01:00:00.0' + (2 || ' second')::interval) as "ss"

-- SQLITE: 
select 
  datetime('1970-01-01 01:00:00.0', '+' || 2 || ' year') yy, 
  datetime('1970-01-01 01:00:00.0', '+' || 2 || ' month') mm, 
  datetime('1970-01-01 01:00:00.0', '+' || 2 || ' day') dd, 
  datetime('1970-01-01 01:00:00.0', '+' || 2 || ' hour') hh, 
  datetime('1970-01-01 01:00:00.0', '+' || 2 || ' minute') mi, 
  datetime('1970-01-01 01:00:00.0', '+' || 2 || ' second') ss

-- DB2: 
select 
  (timestamp '1970-01-01 01:00:00.0' + 2 year) "yy", 
  (timestamp '1970-01-01 01:00:00.0' + 2 month) "mm", 
  (timestamp '1970-01-01 01:00:00.0' + 2 day) "dd", 
  (timestamp '1970-01-01 01:00:00.0' + 2 hour) "hh", 
  (timestamp '1970-01-01 01:00:00.0' + 2 minute) "mi", 
  (timestamp '1970-01-01 01:00:00.0' + 2 second) "ss"
from "SYSIBM"."DUAL"

-- ORACLE: 
select 
  (timestamp '1970-01-01 01:00:00.0' + numtoyminterval(2, 'year')) "yy", 
  (timestamp '1970-01-01 01:00:00.0' + numtoyminterval(2, 'month')) "mm", 
  (timestamp '1970-01-01 01:00:00.0' + numtodsinterval(2, 'day')) "dd", 
  (timestamp '1970-01-01 01:00:00.0' + numtodsinterval(2, 'hour')) "hh", 
  (timestamp '1970-01-01 01:00:00.0' + numtodsinterval(2, 'minute')) "mi", 
  (timestamp '1970-01-01 01:00:00.0' + numtodsinterval(2, 'second')) "ss"
from dual

-- SQLSERVER: 
select 
  dateadd(yy, 2, '1970-01-01 01:00:00.0') [yy], 
  dateadd(mm, 2, '1970-01-01 01:00:00.0') [mm], 
  dateadd(dd, 2, '1970-01-01 01:00:00.0') [dd], 
  dateadd(hh, 2, '1970-01-01 01:00:00.0') [hh], 
  dateadd(mi, 2, '1970-01-01 01:00:00.0') [mi], 
  dateadd(ss, 2, '1970-01-01 01:00:00.0') [ss]

-- SYBASE: 
select 
  dateadd(yy, 2, '1970-01-01 01:00:00.0') [yy], 
  dateadd(mm, 2, '1970-01-01 01:00:00.0') [mm], 
  dateadd(dd, 2, '1970-01-01 01:00:00.0') [dd], 
  dateadd(hh, 2, '1970-01-01 01:00:00.0') [hh], 
  dateadd(mi, 2, '1970-01-01 01:00:00.0') [mi], 
  dateadd(ss, 2, '1970-01-01 01:00:00.0') [ss]
from [SYS].[DUMMY]

If you’ve read thus far, you’ve realised that you shouldn’t perform date-time arithmetic using fractional days as many people think they should when they write Oracle SQL. Just use built-in functions and/or interval data types. Always!

Tags: , , , , ,

2 responses to “You’re Very Likely to Have Gotten SQL Date Time Arithmetic Wrong!”

  1. vladmihalcea says :

    I never got to memorize all those Date Time functions, so every time I have to google for what I want to achieve. I’m glad you added support for it.

    • lukaseder says :

      There was some support before, but mostly using INTERVAL data types. This didn’t work flawlessly in all databases, so we’ve bent the SQL standard a little to find this pragmatic solution…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,628 other followers

%d bloggers like this: