SQL Date/Time Maths

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

DATE_ADD (synonym of ADDDATE)
PERIOD_ADD
ADDDATE
ADDTIME

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

SUBDATE
SUBTIME

Difference

MS-SQL

DATEDIFF

MySQL

DATEDIFF
TIMEDIFF
TIMESTAMPDIFF
PERIOD_DIFF

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()

MySQL Shell

Microsoft SQL Server

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

PostgreSQL

EXTRACT(epoch FROM now())*1000

Bibliography

MS-SQLhttps://www.w3schools.com/sqL/func_sqlserver_dateadd.asp
MySQLhttps://www.w3resource.com/mysql/date-and-time-functions/mysql-datediff-function.phphttps://www.w3resource.com/mysql/date-and-time-functions/mysql-timediff-function.php
UNIXTIMEhttps://currentmillis.com
Date Range Predicateshttps://stackoverflow.com/questions/60578894/problem-in-getting-records-from-last-13-months-in-mysql

https://www.explainxkcd.com/wiki/index.php/2867:_DateTime