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.

The 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:

microsecond

millisecond

second

minute

hour

day

week

month

quarter

year

decade

century

millenium

The DATE_TRUNC syntax looks like this: DATE_TRUNC('interval',timestamp) .

For example, SELECT DATE_TRUNC('day','2015-04-12 14:44:18') would return a result of 2015-04-12 00:00:00 .

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?

Try 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 DATE_TRUNC .

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 month and 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

Finally, use 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.

Mode Analytics

The 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.

Running SELECT NOW() at 9:00am UTC on October 11th, 2016 would result in 2016-10-11 09:00:00 .

The 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 2016-10-11 .

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'

Mode Analytics

In addition to hour , you can use any of the following intervals:

microseconds

milliseconds

second

minute

hour

day

week

month

year

decade

century

millennium

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 DATE_TRUNC and CURRENT_DATE functions.

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'

Mode Analytics

Isolating hour-of-day and day-of-week with EXTRACT

The 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 2 .

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 millennium to 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

Use the COUNT function to tally orders, and then GROUP BY hour. (Since hour is the first value in your SELECT statement, you can GROUP BY 1 ).

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.

Mode Analytics

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 dow and 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.

Mode Analytics

Calculating time elapsed with AGE

The 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 AGE function.

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 ( created ).

SELECT name, AGE(created) AS account_age FROM modeanalytics.customer_accounts

Mode Analytics

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 started_at to ended_at for each profile using the AGE function.

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

Mode Analytics

Want some more practice? Learn Learn SQL and Python using real-world data with our free tutorials.

Recommended articles