Skip to content

Instantly share code, notes, and snippets.

@henryivesjones

henryivesjones/postgresql_date_timestamp_interval_cheat_sheet.md

Created February 14, 2023 16:56
Show Gist options
  • Save henryivesjones/ebd653acbf61cb408380a49659e2be97 to your computer and use it in GitHub Desktop.
Save henryivesjones/ebd653acbf61cb408380a49659e2be97 to your computer and use it in GitHub Desktop.
Download ZIP
PostgreSQL DATE, TIMESTAMP, and INTERVAL cheat sheet
Raw
postgresql_date_timestamp_interval_cheat_sheet.md

PostgreSQL DATE, TIMESTAMP, and INTERVAL cheat sheet

Working with DATE, TIMESTAMP, and INTERVAL in PostgreSQL can be confusing. In this article I will go over the three date/time related data types, and the two most useful date/time functions: DATE_PART and DATE_TRUNC. Finally, I will provide some real life examples of how these types and functions can be used within queries.

Types

PostgreSQL Date/Time Documentation

DATE

The DATE type contains the year, month, and day of a date. It is not possible to do any type of time related functions on a DATE without first converting it to a TIMESTAMP. Subtracting two DATE values from one another results in an INT representing the # of days between.

TIMESTAMP

The TIMESTAMP type contains a year, month, day, hour, minute, second, and microsecond. This is the type that I most often use.

TIMESTAMP can be WITH TIME ZONE or WITHOUT TIME ZONE. The default TIMESTAMP is WITHOUT TIME ZONE and WITH TIME ZONE can be abbreviated as TIMESTAMPZ.

Internally TIMESTAMP is stored in UTC regardless of if it is WITH TIME ZONE or WITHOUT TIME ZONE. For columns or values of the WITH TIME ZONE type, the value returned to you by the server depends only on the TIME ZONE of the client session.

INTERVAL

The INTERVAL type refers to the difference between two dates. It can be constructed literally, or by subtracting a TIMESTAMP value from another. An interval can contain years, months, weeks, days, hours, seconds, and microseconds. Each part can be either positive or negative. However not all of these units play nicely together.

years and months can be rolled up together, weeks are rolled into days, and hours, minutes, seconds, and milliseconds can be rolled up together. However these three groups cannot be rolled up together and remain distinct within the interval.

Here are some examples of how this effects intervals:

INTERVAL '1 year 1 month'               -- '1 year 1 mon'
INTERVAL '1 year 14 months'             -- '2 years 2 mons'

INTERVAL '1 week'                       -- '7 days'
INTERVAL '1 week 6 days'                -- '13 days'
INTERVAL '52 weeks'                     -- '364 days'
INTERVAL '366 days'                     -- '366 days'

INTERVAL '1 hour 1 minute'              -- '01:01:00'
INTERVAL '1 hour 61 minutes'            -- '02:01:00'
INTERVAL '48 hours 60 minutes'          -- '49:00:00

INTERVAL '1 year 365 days 24 hours'     -- '1 year 365 days 24:00:00'
INTERVAL '60 weeks 48 hours 12 minutes' -- '420 days 48:12:00'

INTERVAL addition and subtraction

INTERVAL values can be added and subtracted from other INTERVAL values as well as by DATE and TIMESTAMP values. They can also be created by subtracting two TIMESTAMP values.

INTERVAL + INTERVAL = INTERVAL
INTERVAL + DATE = TIMESTAMP
INTERVAL + TIMESTAMP = TIMESTAMP
DATE - DATE = INTEGER
DATE - TIMESTAMP = INTERVAL
TIMESTAMP - TIMESTAMP = INTERVAL

INTERVAL '1 month' + INTERVAL '2 months'              -- '3 mons'
INTERVAL '1 year 24 hours' + INTERVAL '1 year 3 days' -- '2 year 3 days 24:00:00'
INTERVAL '1 year 1 day' - INTERVAL '24:00:00'         -- '1 year 1 day -24:00:00'
INTERVAL '11 months' + INTERVAL '1 month 1 day'       -- '1 year 1 day'

When creating intervals by subtracting two TIMESTAMP values, a year or month value will never be present. The interval will have the number of days, hours, minutes, seconds, and milliseconds between the two TIMESTAMP values. In this case hours will be rolled up into days.

'2023-01-01'::timestamp - '2022-01-01'::timestamp                   -- '365 days'
'2021-01-01'::timestamp - '2020-01-01'::timestamp                   -- '366 days'
'2023-01-15'::timestamp - '2023-01-10'::timestamp                   -- '5 days'
'2023-02-01'::timestamp - '2023-01-01'::timestamp                   -- '31 days'
'2023-01-15 05:00:00'::timestamp - '2023-01-15 00:00:00'::timestamp -- '05:00:00'
'2023-01-18 05:00:00'::timestamp - '2023-01-15 00:00:00'::timestamp -- '3 days 05:00:00'

INTERVAL multiplication and division

INTERVAL values can be multiplied and divided by INT and FLOAT values. This is straightforward when multiplying by an INT.

INTERVAL '1 month' * 2           -- '2 mons'
INTERVAL '1 year 6 months' * 2   -- '3 years'

INTERVAL '5 hours 5 minutes' * 2 -- '10:10:00'
INTERVAL '15 days 24 hours' * 3  -- '45 days 72:00:00'

INTERVAL '1 month 15 days' * 3   -- '3 mons 45 days'
INTERVAL '1 month 15 days' * 12  -- '1 year 180 days'

However when dividing by an INT or FLOAT value, the values can get distorted due to the indeterminate conversion from year to day, month to day, or week to year. (not every year has 365 days, not every month has 30 days, etc...).

This will not result in an error, but the results are not always accurate.

INTERVAL '1 month' / 30          -- '1 day'
INTERVAL '1 year' / 365          -- '23:40:16.4064'
INTERVAL '1 year' / 12           -- '1 mon'
INTERVAL '1 month' / 4           -- '7 days 12:00:00'
INTERVAL '1 month' * 0.5         -- '15 days'
INTERVAL '1 min' * 0.5           -- '00:00:30'
INTERVAL '3 months 30 days' / 30 -- '4 days'

INTERVAL comparison

INTERVAL values can be compared using the =, >=, <, and <= operators. However you must be careful when doing this for the same reasons as INTERVAL division. Comparisons between INTERVAL values containing year and month and INTERVAL values containing day, hour, minute, second, microsecond values cannot always be trusted as they rely on not always correct assumptions about the conversion from year to day and month to day.

This will not result in an error, but the results are not always accurate.

INTERVAL '1 month' < INTERVAL '2 months'               -- TRUE
INTERVAL '1 months 30 days' = INTERVAL '2 months'      -- TRUE
INTERVAL '1 year' > INTERVAL '365 days'                -- FALSE
INTERVAL '12 months 5 days' = INTERVAL '1 year 5 days' -- TRUE
INTERVAL '52 weeks' > INTERVAL '1 year'                -- TRUE
INTERVAL '365 days' > INTERVAL '1 year'                -- TRUE
INTERVAL '365 days' = INTERVAL '1 year'                -- FALSE

Functions

DATE_TRUNC

The DATE_TRUNC function is in my opinion one of the most useful functions when it comes to writing business intelligence or analytical SQL. It is analogous to the mathematical floor function, where instead of specifying the # of decimal places, you specify the part of the date.

This function has the signature DATE_TRUNC(<date_part>, timestamp/date) where <date_part> is a part of a date: (decade, year, quarter, month, week, day, hour, minute, second). The function truncates the given timestamp to the beginning of the given date_part given.

Timestamp Examples:

DATE_TRUNC('year', '2023-02-14 05:06:07'::timestamp)      -- '2023-01-01 00:00:00'
DATE_TRUNC('quarter', '2023-02-14 05:06:07'::timestamp)   -- '2023-01-01 00:00:00'
DATE_TRUNC('month', '2023-02-14 05:06:07'::timestamp)     -- '2023-02-01 00:00:00'
DATE_TRUNC('week', '2023-02-14 05:06:07'::timestamp)      -- '2023-02-13 00:00:00'
DATE_TRUNC('day', '2023-02-14 05:06:07'::timestamp)       -- '2023-02-14 00:00:00'
DATE_TRUNC('hour', '2023-02-14 05:06:07'::timestamp)      -- '2023-02-14 05:00:00'
DATE_TRUNC('minute', '2023-02-14 05:06:07'::timestamp)    -- '2023-02-14 05:06:00'
DATE_TRUNC('second', '2023-02-14 05:06:07'::timestamp)    -- '2023-02-14 05:06:07'

Note about the week date_part. The first day of the week is Monday.

The DATE_TRUNC function also works with intervals. Because this is a floor function, if the given interval is less than the date_part used then a zero interval is returned.

Interval Examples:

DATE_TRUNC('year', INTERVAL '5 days')                                               -- '00:00:00'
DATE_TRUNC('year', INTERVAL '1 year 2 months 3 days 4 hours 5 minutes 6 seconds')   -- '1 year'
DATE_TRUNC('month', INTERVAL '1 year 2 months 3 days 4 hours 5 minutes 6 seconds')  -- '1 year 2 mons'
DATE_TRUNC('day', INTERVAL '1 year 2 months 3 days 4 hours 5 minutes 6 seconds')    -- '1 year 2 mons 3 days'
DATE_TRUNC('hour', INTERVAL '1 year 2 months 3 days 4 hours 5 minutes 6 seconds')   -- '1 year 2 mons 3 days 04:00:00'
DATE_TRUNC('minute', INTERVAL '1 year 2 months 3 days 4 hours 5 minutes 6 seconds') -- '1 year 2 mons 3 days 04:05:00'
DATE_TRUNC('second', INTERVAL '1 year 2 months 3 days 4 hours 5 minutes 6 seconds') -- '1 year 2 mons 3 days 04:05:06'

You cannot use the date_part of week when using DATE_TRUNC on an INTERVAL.

DATE_PART

The DATE_PART function can also be very useful. PostgreSQL provides two very similar functions DATE_PART and EXTRACT with different syntax, but identical (DATE_PART returns a double, which can lead to some loss of precision) behavior. I prefer to use DATE_PART as it matches the syntax used by DATE_TRUNC.

The DATE_PART function extracts a part or derivative value of the date/time from a given DATE, TIMESTAMP, or INTERVAL value.

This function has the signature DATE_PART(<date_part>, timestamp/date/interval) where <date_part> is a part of a date/timestamp: (year, quarter, month, day, hour, minute, second), or it is a derivative value of the DATE or TIMESTAMP value.

The date_part derivative values are as follows:

  • century: The century of the given DATE or TIMESTAMP (We are in the 21st Century in 2023)
  • decade: The year / 10
  • dow: Day of week from Sunday(0) to Saturday(6)
  • doy: Day of year from 1 to 365/366
  • epoch: The number of seconds since the epoch (1970-01-01 00:00:00). Or the total # of seconds in an INTERVAL
  • isodow: The ISO 8601 day of the week from Monday(1) to Sunday(7)
  • isoyear: The ISO 8601 week numbering year. Very early on in the year it may differ from the gregorian year.
  • week: The ISO 8601 week number. Week 1 of a given year contains January 4. This means that very early on in the year you can be in week 53 of the previous year.
  • timezone: The timezone offset in seconds.
  • timezone_hour: The hour component of the timezone offset.
  • timezone_minute: The minute component of the timezone offset.

Timestamp Examples

DATE_PART('year', '2023-02-14 05:06:07'::timestamp)    -- 2023
DATE_PART('quarter', '2023-02-14 05:06:07'::timestamp) -- 1
DATE_PART('month', '2023-02-14 05:06:07'::timestamp)   -- 2
DATE_PART('day', '2023-02-14 05:06:07'::timestamp)     -- 14
DATE_PART('hour', '2023-02-14 05:06:07'::timestamp)    -- 5
DATE_PART('minute', '2023-02-14 05:06:07'::timestamp)  -- 6
DATE_PART('second', '2023-02-14 05:06:07'::timestamp)  -- 7

DATE_PART('century', '2023-02-14 05:06:07'::timestamp) -- 21
DATE_PART('decade', '2023-02-14 05:06:07'::timestamp)  -- 202
DATE_PART('dow', '2023-02-14 05:06:07'::timestamp)     -- 2
DATE_PART('doy', '2023-01-01 05:06:07'::timestamp)     -- 1
DATE_PART('doy', '2023-02-14 05:06:07'::timestamp)     -- 45
DATE_PART('epoch', '2023-02-14 05:06:07'::timestamp)   -- 1676351167
DATE_PART('epoch', '1970-01-01 00:00:00'::timestamp)   -- 0
DATE_PART('isodow', '2023-02-14 05:06:07'::timestamp)  -- 2
DATE_PART('week', '2023-02-14 05:06:07'::timestamp)    -- 7
DATE_PART('week', '2005-01-01 05:06:07'::timestamp)    -- 53
DATE_PART('isoyear', '2023-01-01 05:06:07'::timestamp) -- 2022
DATE_PART('isoyear', '2023-02-14 05:06:07'::timestamp) -- 2023

-- The timezone values change according to your client settings, not the value given.
-- My client is connected at -5:00 so my values will reflect this.
DATE_PART('timezone', '2023-02-14 05:06:07 -8:00'::timestamp with time zone)        -- -18000
DATE_PART('timezone_hour', '2023-02-14 05:06:07 -8:00'::timestamp with time zone)   -- -5
DATE_PART('timezone_minute', '2023-02-14 05:06:07 -8:00'::timestamp with time zone) -- 0

Interval Examples

You must be careful when using DATE_PART from INTERVAL values. INTERVAL values store their information in parts: year/month, week/day, and hour/min/sec and perform rollups within those parts. Thus when extracting a part from an INTERVAL the results might not tell the full story of the INTERVAL.

Certain DATE_PARTS are not available for intervals: (dow, doy, isodow, isoyear, week, timezone, timezone_hour, timezone_minute)

DATE_PART('year', INTERVAL '2 years 1 month')          -- 2
DATE_PART('year', INTERVAL '12 months')                -- 1
DATE_PART('year', INTERVAL '1 year 13 months')         -- 2
DATE_PART('year', INTERVAL '1 year 366 days')          -- 1

DATE_PART('month', INTERVAL '11 months')               -- 11
DATE_PART('month', INTERVAL '1 year 13 months')        -- 1
DATE_PART('month', INTERVAL '12 months')               -- 0

DATE_PART('day', INTERVAL '31 days 4 hours')           -- 31
DATE_PART('day', INTERVAL '2 weeks')                   -- 14
DATE_PART('day', INTERVAL '2 weeks 8 days')            -- 22
DATE_PART('day', INTERVAL '8 days 24 hours')           -- 8

DATE_PART('hour', INTERVAL '31 days 4 hours')          -- 4
DATE_PART('hour', INTERVAL '4 hours 5 minutes')        -- 4
DATE_PART('hour', INTERVAL '4 hours 61 minutes')       -- 5

DATE_PART('minutes', INTERVAL '40 minutes')            -- 40
DATE_PART('minutes', INTERVAL '65 minutes')            -- 5
DATE_PART('minutes', INTERVAL '40 minutes 70 seconds') -- 41

DATE_PART('epoch', INTERVAL '1 minute')                -- 60
DATE_PART('epoch', INTERVAL '1 minute 20 seconds')     -- 80
DATE_PART('epoch', INTERVAL '1 day 1 hour 3 minutes')  -- 90180


-- be careful when using epoch with intervals that contain months or years as they will use estimations.
DATE_PART('epoch', INTERVAL '1 month')                 -- 2592000
DATE_PART('epoch', INTERVAL '1 year')                  -- 31557600

Functional Examples

Time-Series Data

The easiest way to query and aggregate data in a time-series format is with the DATE_TRUNC function. This way data from multiple years can be easily combined.

SELECT DATE_TRUNC('month', o.timestamp_col) AS date_month
    , SUM(o.total_price) AS revenue
FROM orders o
GROUP BY 1
ORDER BY 1 ASC;

Month over Month comparison with Percent Change

I will leverage DATE_TRUNC to first aggregate the data, and then I will utilize an INTERVAL in my join to align the consecutive rows. This will allow data from multiple years to be queried together.

WITH monthly_revenue AS (
    SELECT DATE_TRUNC('month', o.timestamp_col) AS date_month
        , SUM(o.total_price) AS revenue
    FROM orders o
    GROUP BY 1
)
SELECT current.date_month
    , current.revenue AS revenue
    , previous.revenue AS prev_revenue
    , (current.revenue - previous.revenue) / NULLIF(previous.revenue, 0) AS pct_change
FROM monthly_revenue current
LEFT JOIN monthly_revenue previous ON
    current.date_month - INTERVAL '1 month' = previous.date_month
ORDER BY 1 ASC;

Week of Year Time-Series with Year over Year Percent Change

When looking at data aggregated by week, it is very difficult to do a Year-over-Year comparison. To do this we must leverage the ISO 8601 week of year convention. Each week in a year is given a number from 1 to 53. We can extract the week and year using DATE_PART to align the rows.

WITH weekly_revenue AS (
    SELECT DATE_PART('week', o.timestamp_col) AS week_of_year
        , DATE_PART('year', o.timestamp_col) AS year
        , SUM(o.total_price) AS revenue
    FROM orders o
    GROUP BY 1, 2
)
SELECT current.week_of_year
    , current.year
    , current.revenue AS revenue
    , previous.revenue AS prev_revenue
    , (current.revenue - previous.revenue) / NULLIF(previous.revenue, 0) AS pct_change
FROM weekly_revenue current
LEFT JOIN weekly_revenue previous ON
    current.week_of_year = previous.week_of_year
    AND current.year - 1 = previous.year
ORDER BY 1 ASC;

Offset Quarters

Some companies have quarters which are offset from the conventional Q1 starting on Janaury 1. For instance, Q1 2024 might start on 2023-02-01. We can use a combination of INTERVAL and DATE_PART to accommodate for this.

SELECT DATE_PART('year', o.timestamp_col + INTERVAL '1 year -1 month') AS year
    , DATE_PART('quarter', o.timestamp_col + INTERVAL '1 year -1 month') AS quarter
    , SUM(o.total_price) AS revenue
FROM orders o
GROUP BY 1, 2
ORDER BY 2 ASC, 1 ASC;

Classifying sessions from raw page views

Given a table of timestamped page views, we can leverage TIMESTAMP differences and DATE_PART INTERVAL comparisons to create sessions from these page views. We will define the end of a session as a duration of longer than 20 minutes between page views.

WITH lagged_lead_views AS (
    SELECT v.id
        , v.timestamp_col
        , v.user_id
        , LAG(v.timestamp_col)
            OVER (
                PARTITION BY v.user_id
                ORDER BY v.timestamp_col ASC
            ) AS previous_view_timestamp
        , LEAD(v.timestamp_col)
            OVER (
                PARTITION BY v.user_id
                ORDER BY v.timestamp_col ASC
            ) AS next_view_timestamp
    FROM views v
), session_start_views AS (
    SELECT lv.id
        , lv.timestamp_col as session_start_timestamp
        , lv.user_id
        , ROW_NUMBER()
            OVER (
                PARTITION BY lv.user_id
                ORDER BY lv.timestamp_col ASC
            ) AS user_session_id
    FROM lagged_lead_views lv
    WHERE lv.previous_view_timestamp is NULL
        OR DATE_PART('epoch', lv.timestamp_col - lv.previous_view_timestamp) > (20 * 60) -- 20 minutes in seconds
), session_end_views AS (
    SELECT lv.id
        , lv.timestamp_col as session_end_timestamp
        , lv.user_id
        , ROW_NUMBER()
            OVER (
                PARTITION BY lv.user_id
                ORDER BY lv.timestamp_col ASC
            ) AS user_session_id
    FROM lagged_lead_views lv
    WHERE lv.next_view_timestamp is NULL
        OR DATE_PART('epoch', lv.next_view_timestamp - lv.timestamp_col) > (20 * 60) -- 20 minutes in seconds
)
SELECT ssv.user_id
    , ssv.session_start_timestamp
    , sev.session_end_timestamp
FROM session_start_views ssv
INNER JOIN session_end_views sev ON
    ssv.user_id = sev.user_id
    AND ssv.user_session_id = sev.user_session_id
ORDER BY 2 ASC;
@henryivesjones
Copy link
Author

INTERVAL '1 year' / 365 -- '23:40:16.4064'

Assuming 30-day months and 24-hour days, I compute 1 year / 365 (to 100us precision) as 23:40:16.4384.

@devbww
23:40:16.4064 is what postgres returns.

SELECT INTERVAL '1 year' / 365;
   ?column?    
---------------
 23:40:16.4064
(1 row)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment