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

```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.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
// 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"),
));
}

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)

// 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"

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!