Jumbo list of most common datetime / timestamp functions in Redshift and MySQL

Sep 8th, 2018 in  by Michael Cho

A list of common expressions for working with date, time, datetime, and timestamp columns in MySQL 5.7.x and Redshift.

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()
  • now()
  • current_timestamp
  • current_timestamp()
* Last 2 are just synonyms of the first.
    The following only work on the leader node and are deprecated, use sysdate or getdate() instead.
  • now()
  • current_timestamp
 

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.


Other articles you may like

Using Python enums in SQLAlchemy models
May 16th, 2018
Bash script to relink alembic migrations
Jun 12th, 2017
Bash script to monitor instances and MySQL
Nov 11th, 2015