Redshift and MySQL's giant list of datetime / timestamp functions

My working day involves working mostly with RDS instances running MySQL and a Redshift data warehouse - both use flavours of SQL which are similar but just slightly different enough I keep forgetting syntax differences!

This is a list of common expressions I use for manipulating date, time, datetime, and timestamp values. Note that it's not a comprehensive list, it's just what I use on a daily basis - refer to the MySQL and Redshift documentation if you want more. Also we currently use MySQL 5.7.x so if you are already on MySQL 8.x then some of these may be incorrect.

Setting session timezone

Objective	Redshift	MySQL
Setting the current session timezone, so that all subsequent queries will be in your specified timezone.	`SET timezone = 'America/Los_Angeles'`	`SET time_zone = "America/Los_Angeles"`

Getting today's date

Objective	Sample Value	Redshift	MySQL
Getting today's date in session timezone.	2018-09-08	`current_date`	`CURDATE()` `CURRENT_DATE` `CURRENT_DATE()` * Last 2 are just synonyms of the first.

Getting current timestamp

Objective Sample Value Redshift MySQL

Objective	Sample Value	Redshift	MySQL
Getting current timestamp in session timezone.	2018-09-08 16:21:07	`sysdate` `getdate()` The following only work on the leader node and are deprecated, use `sysdate` or `getdate()` instead. `now()` `current_timestamp`	`now()` `current_timestamp` `current_timestamp()` * Last 2 are just synonyms of the first.

Getting current timestamp in session timezone.

2018-09-08 16:21:07

sysdate
getdate()

The following only work on the leader node and are deprecated, use sysdate or getdate() instead.

now()
current_timestamp

now()
current_timestamp
current_timestamp()

* Last 2 are just synonyms of the first.

Getting current unix timestamp

Objective	Sample Value	Redshift	MySQL
Get current unix timestamp.	1536451525	`EXTRACT('epoch' FROM sysdate)`	`UNIX_TIMESTAMP()`

Formatting a datetime value

Objective	Sample Value	Redshift	MySQL
Expressing some datetime value in another format.	08 September	`TO_CHAR(sysdate, 'DD Month')`	`DATE_FORMAT(NOW(), '%d %M');`

Extracting some date part from datetime

Objective	Sample Value	Redshift	MySQL
Given a datetime value, extract just the hour / day / minute / etc.	17	`EXTRACT(hour FROM sysdate)` `DATE_PART(hour, sysdate)`	`EXTRACT(HOUR FROM NOW())`
Just getting the date from a datetime	2018-09-08	`TRUNC(sysdate)`

Convert datetime to another timezone

Objective	Sample Value	Redshift	MySQL
Convert a datetime value from one timezone to another.	2018-09-08 16:21:07	`CONVERT_TIMEZONE(‘UTC’, 'US/Alaska', dttm)`	`CONVERT_TZ(dttm, 'UTC', 'US/Alaska')`

Convert unix timestamp to datetime

Objective	Sample Value	Redshift	MySQL
Convert a unix timestamp to a datetime value.	2015-11-13 16:08:01	`TIMESTAMP 'epoch' + 1447430881 * INTERVAL '1 second'` * There's no built-in function for this, but you could look into creating User Defined Functions.	`FROM_UNIXTIME(1447430881)`

Add interval to datetime

Objective	Sample Value	Redshift	MySQL
Add some interval to a datetime value, ie in the future or past.	2018-09-07 14:21:07	`DATEADD('day', 1, sysdate)` * Unlike MySQL, if the last argument is a date it will still return a datetime, just with 00:00 hour values	`DATE_ADD(NOW(), INTERVAL ‘-1 2’ DAY_HOUR)` `DATE_SUB(NOW(), INTERVAL 1 DAY)` `NOW() + INTERVAL 1 DAY` * If first arg is date, it returns date. If timestamp, returns timestamp. DATE_SUB just reverses the sign.

And you'll probably need the Redshift datetime format and MySQL datetime format documentation too.

Calculating difference between 2 datetimes

Objective	Sample Value	Redshift	MySQL
Get the difference in hour, minute, etc.	3	`DATEDIFF(hour, dttm1, dttm2)`	`TIMESTAMPDIFF(HOUR, dttm1, dttm2)`
Get the difference in days.	2	Just use `DATEDIFF`	`DATEDIFF(dttm1, dttm2);`

I hope you find this useful. Periscope also has a list of common differences between MySQL and Redshift.