Home » Software Development » You’re Very Likely to Have Gotten SQL Date Time Arithmetic Wrong!

About Lukas Eder

Lukas is a Java and SQL enthusiast developer. He created the Data Geekery GmbH. He is the creator of jOOQ, a comprehensive SQL library for Java, and he is blogging mostly about these three topics: Java, SQL and jOOQ.

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!
 

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

and many more ....

 

4 comments

  1. I have not seen anything like your top/first SQL statement for at least 15 years.
    The last time it was when I was working on IBM AS-400 and even then the SQL date arithmetic was clearer.
    Are you saying that some people are still doing that?

    The solution to your date arithmetic problem that I found the easiest was to store the dates in predefined time-zone (UTC was my choice as I am based in London).

    • Yes, I’ve seen this so many times. For some reason, these kinds of expressions are among the top Google results. I guess that juniors will always resort to hand-made arithmetic instead of considering actual API… But this can happen in any language – e.g. Java.

  2. Time is a complex concept. Whatever the language, learn correct functions for handling time!

    Another one I see is using string operations to construct relative dates instead using the SQL Server functions DATEADD and DATEDIFF. It makes me groan every time!

Leave a Reply

Your email address will not be published. Required fields are marked *

*


six + = 9

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Do you want to know how to develop your skillset and become a ...

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!
Get ready to Rock!
To download the books, please verify your email address by following the instructions found on the email we just sent you.

THANK YOU!

Close