Michael Cho

Articles · Categories · About · Contact

8 September 2018

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

<p>How to find Redshift current date and other common expressions for working with date, time, datetime, and timestamp columns in MySQL 5.7.x and Redshift.</p>

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

ObjectiveRedshiftMySQL
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

ObjectiveSample ValueRedshiftMySQL
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

ObjectiveSample ValueRedshiftMySQL
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

ObjectiveSample ValueRedshiftMySQL
Get current unix timestamp. 1536451525 EXTRACT('epoch' FROM sysdate) UNIX_TIMESTAMP()

Formatting a datetime value

ObjectiveSample ValueRedshiftMySQL
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

ObjectiveSample ValueRedshiftMySQL
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

ObjectiveSample ValueRedshiftMySQL
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

ObjectiveSample ValueRedshiftMySQL
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

ObjectiveSample ValueRedshiftMySQL
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

ObjectiveSample ValueRedshiftMySQL
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.

© 2026 Michael Cho