Timestamps are great for debugging logs. But for analysis, we want to group by day, week, and month.
Timestamps look like this: 07/04/1776 00:28:39.682, but you’d rather see “July 4, 1776”, “July 1776” or “Q3 - 1776” 🇺🇸. Let’s clean up those messy timestamps in SQL!
In Postgres & Redshift, the to_char function is handy for formatting dates. It takes two arguments: a timestamp and the output format pattern. For example, the following returns “July 04, 1776”:
select to_char('07/04/1776 00:28:39.682'::timestamp, 'Month DD, YYYY');
The next SQL statement returns “Q3 - 1776”, by concatenating “Q” to the quarter and year from the to_char function.
select 'Q' || to_char('07/04/1776 00:28:39.682'::timestamp, 'Q - YYYY');
Pro Tip: Use to_char to format numbers too! See all the possibilities in the official docs.
In MySQL, the date_format function will make those timestamps prettier. Here’s how to make ‘1776-07-04 00:28:39.682’ look like “July 4, 1776”:
select date_format('1776-07-04 00:28:39.682', '%M %e, %Y');
to_char and date_format docs