Formatting and dealing with dates in SQL

Nobody likes dates, especially programmers, but they’re a critical part of pretty much every application. In Javascript you’ve got Moment, but parsing dates in SQL is a bit more complex. This post will run through how you can effectively work with dates in SQL, resolve your issue quickly, and get back to bed.

Part of why writing SQL is annoying is that there are hundreds of different flavors. Syntax is slightly different from MySQL to PostgreSQL (for example), and some dialects have functions that others don’t (e.g. PIVOT in Snowflake). When you’re working with dates, there are prototypes for types of functions: even though the exact syntax might differ between dialects, the idea is the same. We’ll tackle 5 broad categories:

For each function prototype, we’ll provide the right syntax and documentation for 5 of the more popular SQL dialects:

Something that often gets confusing is the difference between DATE and TIMESTAMP. A TIMESTAMP is just a DATE with an additional two levels of precision: fractional seconds and fractional seconds with time zones.

In general, we’ll use “date” in this tutorial but the distinction isn’t super important. Let’s go!

Dates never seem to be in the format you want them to be in.

Computers interpret dates in all different formats, from unixtime to strings and timestamps, and they’re usually not friendly to each other. Here are the function prototypes:

Unix time (which is also called epochs time) is kind of funky: it corresponds to the number of seconds that have elapsed since January 1st, 1970. A typical unix timestamp might look like this: 1284352323.

Here’s how these different conversion functions look across major SQL dialects:

Function MySQL PostgreSQL BigQuery Redshift Presto Convert unix time to date format FROM_UNIXTIME() TO_TIMESTAMP() DATE_FROM_UNIX_DATE() TIMESTAMP 'epoch' + your_timestamp_column * INTERVAL '1 second' FROM_UNIXTIME() Convert date format to unix time UNIX_TIMESTAMP() EXTRACT(EPOCH FROM TIMESTAMP ) UNIX_DATE() DATE_PART(EPOCH, ) TO_UNIXTIME() Convert string to date format STR_TO_DATE() TO_DATE() PARSE_DATE() TO_DATE() DATE_PARSE() Convert date to string format DATE_FORMAT() TO_CHAR() FORMAT_DATE() TO_CHAR() DATE_FORMAT()

Here’s how we’d use these functions in Postgres, with expected inputs and outputs:

There’s a whole “language” of string formatting arguments that developers need to frantically search Google for every time they use them. Almost every single dialect follows the C strftime standard, except for Postgres. These are usually the same across TO_DATE() and FORMAT_DATE().

Subscribe to the Retool monthly newsletter
Once a month, we send out top stories (like this one) along with Retool tutorials, templates, and product releases.

Date rounding lets you lower the specificity of your date; this is useful for aggregations and looking at trends over time. The prototype function here is DATE_TRUNC(), which truncates your date to a lower level of specificity, like month or year. Here’s how things look across dialects:

Function MySQL PostgreSQL BigQuery Redshift Presto Truncate date specificity DATE_FORMAT() DATE_TRUNC() DATE_TRUNC() DATE_TRUNC() DATE_TRUNC()

For a more concrete example of when you’d use a date truncation function, imagine we have a table of order where each row represents an order, and each order has a date. If we want to look at order growth month over month, we’d use a truncation function in Postgres:

The DATE_TRUNC() function grabs the month and year from the date so you can get a monthly view.

If you have a timestamp, date part functions will pick out a particular part of that timestamp; this is useful if you want to display what day of the week a user logged in, what hour someone made an order, or aggregate event data by month to see which months of the year your website gets the most traffic. Our function prototype:

Here’s how it plays out across different types of SQL:

Function MySQL PostgreSQL BigQuery Redshift Presto Extract specific part of date EXTRACT() DATE_PART() EXTRACT() DATE_PART() EXTRACT()

In Postgres, we can extract just the day of a user’s order using DATE_PART():

Each SQL dialect has different approaches for how to specify data parts to extract, so be sure to check the documentation linked in the table above.

If you need to calculate the duration or difference between two dates, most SQL dialects have functions for that. The popular one here is DATE_DIFF():

If you want to get the difference between two dates in days (i.e. how many days exist between date number one and date number two) you’d use something like DATE_DIFF(‘day’, ‘2019-01-01’, ‘2019-01-06’). Weirdly, in some languages, time deltas are their own data type (see, for example, the Pandas package in Python). Here’s how these functions line up:

Function MySQL PostgreSQL BigQuery Redshift Presto Get difference between two dates at some specificity DATEDIFF() Subtraction DATE_DIFF() DATEDIFF() DATE_DIFF()

Here’s how we’d use this in Postgres:

A closely related cousin to time deltas is interval functions.

Intervals let you add and subtract time from dates in SQL. This kind of function is useful for calculating rolling widows, like filtering your data for every record in the past 7 days or the past year.

The first two behave like normal functions, but in Postgres and Presto, using the INTERVAL operator is funky.

Function MySQL PostgreSQL BigQuery Redshift Presto Add time to a date DATE_ADD() + INTERVAL DATE_ADD() DATEADD() DATE_ADD() Subtract time from a date DATE_SUB() - INTERVAL DATE_SUB() DATEADD() with negative values DATE_ADD() with negative values Extract date difference MAKE_INTERVAL() PARSE_DURATION()

The INTERVAL operator in SQL has unusual syntax: you usually need to use the keyword, follow it with a number in single quotes, and then a date interval. In Presto, for example, you can add one day to your date by using <some_date> + INTERVAL ‘1’ day. Notice how the string quotes are only around the quantity (1) and not the chosen interval (day).

If we wanted to see the number of orders users have made in the past 7 days:

And here’s how we’d use the MAKE_INTERVAL() function in Postgres:

Time zones are one of the biggest nightmares in date handling, and chances are you’re not sitting in UTC right now. Thankfully, most SQL dialects have a bunch of functions to handle TZ conversion. Our function prototypes:

Timezones also rear their ugly head when dealing with string parsing and date formatting. Here’s how these functions line up across dialects:

Function MySQL PostgreSQL BigQuery Redshift Presto Add timezone to a date CONVERT_TZ() or system settings AT TIME ZONE In strings AT TIME ZONE AT TIME ZONE Convert date to timezone or convert between timezones CONVERT_TZ() AT TIME ZONE FORMAT_DATE() CONVERT_TIMEZONE() AT TIME ZONE

Here’s how we’d add a timezone to a user’s click event or convert between timezones in Postgres:

In a few of these dialects, you use the same function (AT TIMEZONE) to add a timezone to a date, as well as convert a date that already has a timezone to a different one. MySQL also has a dedicated function (UTC_DATE()) for converting dates to UTC time. For more about MySQL timezones, check out this wonderful cheatsheet.

The last big category of time related functionality is getting the current time. This kind of functionality is useful in similar cases as intervals, like building a rolling window. Because most of these functions return timestamps, they’re often used in conjunction with something like DATE_TRUNC to pick the right intervals.

This is a pretty popular series of functions, so there are often a bunch of aliases. Here’s how things stack up across SQL flavors:

Function MySQL PostgreSQL BigQuery Redshift Presto Get the current timestamp NOW(), CURTIME() CURRENT_DATE or 10 others CURRENT_DATE() CURRENT_DATE NOW()

General best practice is to keep all of your dates and timestamps in UTC time, and display them based on the client’s timezone. If we wanted to log the time that a user logs in using Postgres, here’s what our query might look like:

Keep in mind that some of these functions return a date that’s tied to the timestamp that your system is set to, which can vary pretty widely across database engines. Postgres returns UTC by default when using CURRENT_TIMESTAMP, so no worries here.

The truth is that as useful as some of these dialects are, scripting languages are often easier to parse dates in (Moment.js is a really popular one for Javascript). If you’d prefer to use more fluid programming languages like JS to work with your relational data that's already in a SQL database, give Retool a spin.