Joel Carron, Data Scientist at Mode
October 20, 2016•Updated on January 4, 2022
NaN minute read
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.
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 in Mode Studio and open a new report to begin.
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.
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-3123: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' GROUPBY 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' GROUPBY 1,2 ORDERBY1,2
In Mode, you can build a line chart to visualize the query results.
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
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 7 years, use a
WHERE clause to return only orders that were placed after or exactly at (
>=) the current timestamp (
NOW()) minus an interval of 7 years.
SELECT * FROM demo.orders WHERE occurred_at >= NOW() - interval '7 year'
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'
You can use the same table to find orders placed on this day seven year ago 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 this day). 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 7 years.
SELECT * FROM demo.orders WHERE DATE_TRUNC('day',occurred_at) = CURRENT_DATE - interval '7 year'
EXTRACT date function allows you to isolate subfields such as year or hour from timestamps. Essentially it allows you to extract parts of a date from a datetime expression.
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.
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 GROUPBY 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 GROUPBY 1 ORDERBY1
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.
To determine the average volume of orders that occurred by weekday, use
EXTRACT and the
dow (day of the week) 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 GROUPBY 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 GROUPBY1,2) a WHERE dow NOTIN (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.
AGE date function calculates how long ago an event occurred. It returns a value representing the number of years, months, and days an event happened or the difference between two given arguments.
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'.
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
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 GROUPBY 1 ORDERBY 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(EPOCHFROM AVG(AGE(ended_at,started_at))) AS avg_seconds FROM modeanalytics.profile_creation_events GROUPBY 1 ORDERBY 1
As you can see ProstgreSQL date functions are both powerful and useful. They give you the ability to manipulate timestamps and answer the question of when things happen. With their functionality, businesses can better analyze trends in their data and gain actionable insights.
Use this guide to help set up your marketing team in Mode. Better understand the metrics they’re looking for and get inspired with sample charts they can use to accelerate their goals.