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

Microservices! They are everywhere, or at least, the term is. When should you use a microservice architecture? What factors should be considered when making that decision? Do the benefits outweigh the costs? Why is everyone so excited about them, anyway?  Brought to you in partnership with IBM.

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!


Discover how the Watson team is further developing SDKs in Java, Node.js, Python, iOS, and Android to access these services and make programming easy. Brought to you in partnership with IBM.

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 best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

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

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

{{ parent.tldr }}

{{ parent.urlSource.name }}