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:
Did you think about timezones? Did you think about daylight savings time? Did you think about leap seconds?
, 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
to standardise SQL. We’ve blogged about
before, and we’re doing it again, with this simple date time arithmetic test programme:
-- 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!
I never got to memorize all those Date Time functions, so every time I have to google for what I want to achieve. I’m glad you added support for it.
There was some support before, but mostly using
INTERVAL
data types. This didn’t work flawlessly in all databases, so we’ve bent the SQL standard a little to find this pragmatic solution…