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

Are you joining the containers revolution? Start leveraging container management using Platform9's ultimate guide to Kubernetes deployment.

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!


Moving towards a private or Hybrid cloud infrastructure model? Get started with our OpenStack Deployment Models guide to learn the proper deployment model for your organization.

Topics:

Published at DZone with permission of Lukas Eder, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

THE DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

X

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

{{ parent.tldr }}

{{ parent.urlSource.name }}