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 two of our best selling eBooks for FREE!

JPA Mini Book

Learn how to leverage the power of JPA in order to create robust and flexible Java applications. With this Mini Book, you will get introduced to JPA and smoothly transition to more advanced concepts.

JVM Troubleshooting Guide

The Java virtual machine is really the foundation of any Java EE platform. Learn how to master it with this advanced guide!

Given email address is already subscribed, thank you!
Oops. Something went wrong. Please try again later.
Please provide a valid email address.
Thank you, your sign-up request was successful! Please check your e-mail inbox.
Please complete the CAPTCHA.
Please fill in the required fields.

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

  1. Germann Arlington says:

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

    • Lukas Eder says:

      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. Iain Elder says:

    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


seven − 6 =



Java Code Geeks and all content copyright © 2010-2014, Exelixis Media Ltd | Terms of Use | Privacy Policy | Contact
All trademarks and registered trademarks appearing on Java Code Geeks are the property of their respective owners.
Java is a trademark or registered trademark of Oracle Corporation in the United States and other countries.
Java Code Geeks is not connected to Oracle Corporation and is not sponsored by Oracle Corporation.
Do you want to know how to develop your skillset and become a ...
Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

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

Get ready to Rock!
You can download the complementary eBooks using the links below:
Close