Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

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

DZone's Guide to

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

· Java Zone ·
Free Resource

How do you break a Monolith into Microservices at Scale? This ebook shows strategies and techniques for building scalable and resilient microservices.

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!


How do you break a Monolith into Microservices at Scale? This ebook shows strategies and techniques for building scalable and resilient microservices.

Topics:

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}