PostgreSQL Date Functions and 7 Ways to Use Them in Business Analysis

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:

• Rounding off timestamps with DATE_TRUNC function

• Finding events relative to the present time with NOW() and CURRENT_DATE functions

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

• Calculating time elapsed with AGE function

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.

Rounding off timestamps with DATE_TRUNC function

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.

Example: 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-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 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'
 GROUPBY 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'
 GROUPBY 1,2
 ORDERBY1,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 functions

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.

Example: What orders were placed in the last 7 years?

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:

• 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'

Example: What orders were placed this day a 7 years ago?

You can use the same table to find orders placed on this day seven year ago 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 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'

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

Example: 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 
 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.

Example: What's the average weekday order volume?

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

Calculating time elapsed with AGE

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

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

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

Conclusion

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.

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

Download

Data Teams Guide for Marketing Metrics

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.

Download