SQL Date/Time Maths

Addition

MS-SQL

DATEADD

DATEADD(interval, number, date)


SELECT DATEADD(year, 1, '2023/01/09') AS Result; 

intervalyear, yyyy, yy = Yearquarter, qq, q = Quartermonth, mm, m = monthdayofyear, dy, y = Day of the yearday, dd, d = Dayweek, ww, wk = Weekweekday, dw, w = Weekdayhour, hh = hourminute, mi, n = Minutesecond, ss, s = Secondmillisecond, ms = Millisecond

MySQL

Subtraction

MS-SQL

DATEADD

DATEADD(interval, number, date)


SELECT DATEADD(year, -1, '2023/01/09') AS Result; 

intervalyear, yyyy, yy = Yearquarter, qq, q = Quartermonth, mm, m = monthdayofyear, dy, y = Day of the yearday, dd, d = Dayweek, ww, wk = Weekweekday, dw, w = Weekdayhour, hh = hourminute, mi, n = Minutesecond, ss, s = Secondmillisecond, ms = Millisecond

MySQL

Difference

MS-SQL

MySQL

DATEDIFF

Returns number of days between two dates

MySQL

SELECT DATEDIFF('2021-05-17','2021-05-10');

In this example the query returns: 10

TIMEDIFF

MySQL

SELECT TIMEDIFF('2021-05-17 06:59:40.008273','2021-05-10 06:40:00.000000');

Date Range Predicates

UNIXTIME

Oracle

COLUMN ut FORMAT 9999999999999

SELECT ((SYSDATE)-CAST(TO_TIMESTAMP_TZ('01-01-1970 00:00:00+00:00','DD-MM-YYYY HH24:MI:SS TZH:TZM') AS DATE))*86400000 AS ut

  FROM DUAL;

86400000 is 24*60*60*1000

MySQL

SELECT UNIX_TIMESTAMP() * 1000

Javascript

Date.now() // or: new Date().getTime()

Microsoft SQL Server

DATEDIFF(ms,'1970-01-01 00:00:00',GETDATE())

PostgreSQL

EXTRACT(epoch FROM now())*1000

Bibliography