Timestamps are crucial to business analysis for a very simple reason: they tell you when things happen. Imagine trying to suss out trends in your data, like monthly web traffic, or quarterly earnings, or daily order volume without knowing when events occurred. It'd be a nightmare.
PostgreSQL offers a variety of date functions for manipulating timestamps. To separate the useful from the obscure, we're sharing how-tos for the most frequently used Postgres date functions and business scenarios where they come in handy.
We've made the data for each example available in the Mode Public Warehouse. Try each date function in Mode as you work your way through these examples. Sign up for an account and open a new report to begin.
Rounding off timestamps with DATE_TRUNC
DATE_TRUNC function rounds a timestamp value to a specified interval, which allows you to count events. You can round off a timestamp to the following units of time:
DATE_TRUNC syntax looks like this:
SELECT DATE_TRUNC('day','2015-04-12 14:44:18') would return a result of
For a more detailed explanation of
DATE_TRUNC (and a printable reference you can keep at your desk!), check out this post.
How has web traffic changed over time?
DATE_TRUNC for yourself by querying the table modeanalytics.web_events, which contains sample records of website visits, including an
occurred_at column. You can isolate the month of the visit with
SELECT DATE_TRUNC('month',occurred_at) AS month FROM demo.web_events WHERE occurred_at BETWEEN '2015-01-01' AND '2015-12-31 23:59:59'
To return a count of web visits each month by channel, add the
channel column and a
COUNT to the
SELECT statement, then group by
channel. (Since month and channel are the first two values in your
SELECT statement, you can
GROUP BY 1,2), like this:
SELECT DATE_TRUNC('month',occurred_at) AS month, channel, COUNT(id) AS visits FROM demo.web_events WHERE occurred_at BETWEEN '2015-01-01' AND '2015-12-31 23:59:59' GROUP BY 1,2
ORDER BY 1,2 to organize your results chronologically (by month) and alphabetically (by channel).
SELECT DATE_TRUNC('month',occurred_at) AS month, channel, COUNT(id) AS visits FROM demo.web_events WHERE occurred_at BETWEEN '2015-01-01' AND '2015-12-31 23:59:59' GROUP BY 1,2 ORDER BY 1,2
In Mode, you can build a line chart to visualize the query results.
Finding events relative to the present time with NOW() and CURRENT_DATE
NOW() date function returns the current timestamp in UTC (if the time zone is unspecified). You can subtract intervals from
NOW() to pull events that happened within the last hour, the last day, the last week, etc.
SELECT NOW() at 9:00am UTC on October 11th, 2016 would result in
CURRENT_DATE function only returns the current date, not the whole timestamp. Running
SELECT CURRENT_DATE at 9:00am UTC on October 11th, 2016 would return
What orders were placed in the last 12 hours?
The table demo.orders contains sample records of all orders, including an
occurred_at timestamp column in UTC.
To find orders placed in the last 12 hours, use a
WHERE clause to return only orders that were placed after or exactly at (
>=) the current timestamp (
NOW()) minus an interval of 12 hours.
SELECT * FROM demo.orders WHERE occurred_at >= NOW() - interval '12 hour'
In addition to
hour, you can use any of the following intervals:
You can also combine different intervals in the same expression like this:
interval '4 hours 3 minutes'
What orders were placed yesterday?
You can use the same table to find yesterday's orders by combining the
Start by using a
DATE_TRUNC function to round your
occurred_at values by day (since we want to know if something happened yesterday). Then use a
WHERE clause to return only values where the
occurred_at day is equal to the current date (using the
CURRENT_DATE function) minus an interval of one day.
SELECT * FROM demo.orders WHERE DATE_TRUNC('day',occurred_at) = CURRENT_DATE - interval '1 day'
Isolating hour-of-day and day-of-week with EXTRACT
EXTRACT date function allows you to isolate subfields such as year or hour from timestamps.
Here's the syntax:
EXTRACT(subfield FROM timestamp). Running
EXTRACT(month FROM '2015-02-12') would return a result of
Keep in mind that while the example below focuses on the subfield hour (hour-of-day), you have many other subfields at your disposal ranging from
microsecond. You can check out the comprehensive list of available subfields here.
How many orders are placed each hour of the day?
A company running a fulfillment center might want to staff more employees when the bulk of orders comes in. To figure out when orders are placed throughout the day, you can use the
EXTRACT function and the
hour subfield to isolate the hour-of-day (from 0 to 23) in which an order occurred.
SELECT EXTRACT(hour from occurred_at) AS hour FROM demo.orders
SELECT EXTRACT(hour from occurred_at) AS hour, COUNT(*) AS orders FROM demo.orders GROUP BY 1
Finally, to organize your results sequentially, use
ORDER BY 1.
SELECT EXTRACT(hour from occurred_at) AS hour, COUNT(*) AS orders FROM demo.orders GROUP BY 1 ORDER BY 1
And here are the results! Looks like it might help to have some extra workers on the clock early in the morning and around lunch time.
What's the average weekday order volume?
To determine the average volume of orders that occurred by weekday, use
EXTRACT and the
dow subfield to isolate the day-of-week (from 0-6, where 0 is Sunday) in which an order occurred.
SELECT EXTRACT(dow from occurred_at) AS dow FROM demo.orders
Next, round the order timestamps by day with
DATE_TRUNC. Taking a
COUNT of orders grouped by
day will return the number of orders placed each day along with the corresponding day-of-week.
SELECT EXTRACT(dow from occurred_at) AS dow, DATE_TRUNC('day',occurred_at) AS day, COUNT(id) AS orders FROM demo.orders GROUP BY 1,2
To find the average weekday order volume, use the previous query as a subquery (aliased as
a). Take the average of orders (using the
AVG() function), and then use a
WHERE clause to filter out Saturdays and Sundays.
SELECT AVG(orders) AS avg_orders_weekday FROM ( SELECT EXTRACT(dow from occurred_at) AS dow, DATE_TRUNC('day',occurred_at) AS day, COUNT(id) AS orders FROM demo.orders GROUP BY 1,2) a WHERE dow NOT IN (0,6)
Big Number charts are great for displaying aggregated metrics. To keep your eye on order volume, gather metrics like this into one dashboard.
Calculating time elapsed with AGE
AGE date function calculates how long ago an event occurred. The syntax is pretty straightforward: apply
AGE() to a single timestamp, and your query will return the amount of time since that event took place. Running
SELECT AGE( '2010-01-01' ) on January 1st, 2011 would return a result of
1 years 0 months 0 days.
AGE() can also determine how much time passed between two events. Instead of putting a single timestamp inside the parentheses, insert both timestamps (starting with the most recent timestamp) and separate them with a comma. Running
SELECT AGE( '2012-12-01','2010-01-01'
) would return
2 years 11 months 0 days.
Note that this application of the
AGE function is equivalent to subtracting the timestamps:
SELECT '2012-12-01' - '2010-01-01'.
How old is a customer account?
Suppose your sales team wants to personalize greetings based on how long the customer has been using your product. You can find how much time has elapsed since account creation using the
The table modeanalytics.customer_accounts contains records of sample customer accounts. Select the column of account names (
name) and apply the
AGE() function to the column of timestamps showing when each account was created (
SELECT name, AGE(created) AS account_age FROM modeanalytics.customer_accounts
How long does it take users to complete their profile each month, on average?
The table modeanalytics.profilecreationevents contains sample data of users who created a profile, including start and end timestamps.
To find the average time to complete a profile each month, start by finding the time it took each user to complete a profile as well as the month in which the profile creation process was started. First, round the
started_at timestamp by month, using the
DATE_TRUNC function. Next, find the time elapsed from
ended_at for each profile using the
SELECT DATE_TRUNC('month',started_at) AS month, AGE(ended_at,started_at) time_to_complete FROM modeanalytics.profile_creation_events
Find the average for each month by applying the
AVG function to the elapsed time value (your
AGE statement) and grouping by month.
SELECT DATE_TRUNC('month',started_at) AS month, AVG(AGE(ended_at,started_at)) AS avg_time_to_complete FROM modeanalytics.profile_creation_events GROUP BY 1 ORDER BY 1
To return values in a consistent unit for charting, apply the
EXTRACT function and epoch subfield to your values to return results as a count of seconds.
SELECT DATE_TRUNC('month',started_at) AS month, EXTRACT(EPOCH FROM AVG(AGE(ended_at,started_at))) AS avg_seconds FROM modeanalytics.profile_creation_events GROUP BY 1 ORDER BY 1