Temporal Functions in SQL

The topic of temporal functions covers date manipulations. In this blog post, we’ll inspect commonly used SQL functions in this category.

Interval Calculations

As you would naturally expect, you can execute date calculations in DQL, where you add or subtract values to or from base dates. The listing below contains an example in which we add three days to req_dlv_date by using the INTERVAL keyword. Note that INTERVAL is followed by a natural language input, and PostgreSQL is able to handle that.

SELECT

   id,

   req_dlv_date,

   req_dlv_date + INTERVAL '3 days' AS dlv_date_deadline

FROM

   customer_order

WHERE

   req_dlv_date IS NOT NULL;

The results set can be seen in this table, which lists the original dates and calculated dates next to each other.

We can use INTERVAL for time values as well. Take a look at the following listing, where we add 10 minutes to existing timestamp values.

SELECT

   id,

   order_ts,

   order_ts + INTERVAL '10 minutes' AS order_ts_plus_10

FROM

   customer_order;

The results set in the below table is how you expect it to be—10 minutes have been added to the original order timestamp.

As an exercise, you can try further natural language INTERVAL values. It’s fun, and most of your attempts will produce valid results!

Current Date and Time

Our DQL toolbox contains three practical keywords we use to fetch the current temporal values, and they are demonstrated in this listing.

SELECT

   CURRENT_DATE AS cda,

   CURRENT_TIME AS cti,

   CURRENT_TIMESTAMP AS cts;

Although the results set will be similar to the one in the table below, the exact values will differ in your attempt. You’ll get the temporal values of the date and time you’re executing in the sample code.

We’ll use some of these keywords in the upcoming examples.

Date Differences

We can calculate the difference between two dates as well. In the listing below, we calculate two values using that technique. The req_dlv_span entry will represent the duration between the requested delivery date and the order creation date, whereas req_dlv_delay will represent any delay of the delivery.

SELECT

   id AS order_id,

   req_dlv_date - order_ts AS req_dlv_span,

   CURRENT_DATE - req_dlv_date AS req_dlv_delay

FROM

   customer_order

WHERE

   req_dlv_date IS NOT NULL;

Check the result set in the next table. The req_dlv_span entry contains days and hours, simply because order_ts is a timestamp column containing the date and time, so the result contains high granularity including the time difference as well. On the other hand, req_dlv_delay contains a simple day difference because both CURRENT_DATE and req_dlv_date are dates without time.

Here’s a practical tip: you can combine aggregate functions with date differences as in this listing, where we calculate average values for previous values.

SELECT

   AVG(req_dlv_date - order_ts) AS avg_span,

   AVG(CURRENT_DATE - req_dlv_date) AS avg_delay

FROM

   customer_order

WHERE

   req_dlv_date IS NOT NULL;

The results set in this table is exactly what you’d expect.

Extracting Date Parts

Using the EXTRACT keyword, we can extract day, month, year, hour, etc., values from a given date. The listing below showcases a query in which many extraction options are demoed. Most are self-explanatory, but DOW stands for the day of the week and retrieves a value of 1 for Monday, 2 for Tuesday, etc.

SELECT

   id,

   invoice_ts,

   EXTRACT( DAY FROM invoice_ts ) AS dy,

   EXTRACT( MONTH FROM invoice_ts ) AS mo,

   EXTRACT( YEAR FROM invoice_ts ) AS yr,

   EXTRACT( HOUR FROM invoice_ts ) AS hr,

   EXTRACT( MINUTE FROM invoice_ts ) AS mn,

   EXTRACT( SECOND FROM invoice_ts ) AS sc,

   EXTRACT( DOW FROM invoice_ts ) AS dw

FROM

   invoice

WHERE

   id = 501;

The results of the query will be as in this table. Note how the date has been sliced and diced into different columns.

As in many other DQL functions, we can use extracted date parts in WHERE conditions as long as we ignore the aforementioned performance penalty. This listing showcases such a query, which calls invoices created on a Monday.

SELECT

   id,

   invoice_ts

FROM

   invoice

WHERE

   EXTRACT( dow FROM invoice_ts ) = 1;

The results of the query will be as in this table. Since 2024-09-23 is a Monday, we get invoices created on that date.

Time Zones

Time zones can be a rather important subject, especially if you’re dealing with international data from different continents. In PostgreSQL, we have two primary data types for handling time zones that are explained in this table.

Now that you understand your data type options, let’s move on to common functions related to the subject.

To set the time zone of the current session, we can use the SET TIMEZONE command as in the listing below. Natural language inputs are supported for time zone names, and this is a convenient and clear approach.

SET TIMEZONE = 'America/New_York';

Usable time zone names are stored in the pg_timezone_names table. You can query this table to get a list of usable expressions.

To convert an existing timestamp to another time zone, we use the AT TIME ZONE expression. For instance, our delivery.delivery_ts is a timestamp field (without a time zone). This listing demonstrates the time zone conversion in that field.

SELECT

   id,

   delivery_ts,

   delivery_ts AT TIME ZONE 'America/New_York' AS ny_to_local

FROM

   delivery

WHERE

   id = 301;

The time zone conversion results can be seen in the following table. The database engine has assumed that the original timestamp of 2024-09-23 15:47:22 was recorded at America/New_York and has converted it to the session time zone as 2024-09-23 22:47:22+03. The output value may be different depending on your local time zone, but note that ny_to_local has a time zone suffix of +03.

If your input timestamp is of the timestamptz type (with a time zone), the database engine already knows the source time zone. AT TIME ZONE simply converts the source time zone to the provided target time zone.

Editor’s note: This post has been adapted from a section of the book SQL: The Practical Guide by Kerem Koseoglu. Dr. Koseoglu is a seasoned software engineer, author, and educator with extensive experience in global software development projects. In addition to his expertise in ABAP, he is also proficient in database-driven development using Python and Swift. He is the author of Design Patterns in ABAP Objects (SAP PRESS), as well as several best-selling technical books in Turkey. He has a Ph.D. in organizational behavior.

This post was originally published 5/2025.