---
title: Temporal Functions in SQL
description: Explore essential SQL temporal functions, including date calculations, current date/time retrieval, date differences, and time zone conversions.
---

[Learn Computing from the Experts | The Rheinwerk Computing Blog](https://blog.rheinwerk-computing.com)

# [Temporal Functions in SQL](https://blog.rheinwerk-computing.com/temporal-functions-in-sql)

 Written by [Rheinwerk Computing](https://blog.rheinwerk-computing.com/author/rheinwerk-computing) | May 14, 2025 1:00:00 PM

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.

 

| **id** | **req\_dlv\_date** | **dlv\_date\_deadline** |
| --- | --- | --- |
| 102 | 2024-10-01 | 2024-10-04 |
| 103 | 2024-10-05 | 2024-10-08 |
| 104 | 2024-09-20 | 2024-09-23 |

 

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.

 

| **id** | **order\_ts** | **order\_ts\_plus\_10** |
| --- | --- | --- |
| 101 | 2024-09-23 15:47:22 | 2024-09-23 15:57:22 |
| 102 | 2024-09-23 15:47:22 | 2024-09-23 15:57:22 |
| 103 | 2024-09-23 15:47:22 | 2024-09-23 15:57:22 |
| 104 | 2024-09-23 15:47:22 | 2024-09-23 15:57:22 |
| 105 | 2024-09-23 15:47:22 | 2024-09-23 15:57:22 |

 

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.

 

| **cda** | **cti** | **cts** |
| --- | --- | --- |
| 2024-10-07 | 13:57:12.224930+3:00 | 2024-10-07 13:57:12.22493+03 |

 

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.

 

| **order\_id** | **req\_dlv\_span** | **req\_dlv\_delay** |
| --- | --- | --- |
| 102 | 7 days 08:12:37 | 5 |
| 103 | 11 days 08:12:37 | 1 |
| 105 | -3 days -15:47:22 | 16 |

 

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.

 

| **av\_span** | **avg\_delay** |
| --- | --- |
| 5 days 00:12:37.655131 | 7.33 |

 

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

 

| **id** | **invoice\_ts** | **dy** | **mo** | **yr** | **hr** | **mn** | **sc** | **dw** |
| --- | --- | --- | --- | --- | --- | --- | --- | --- |
| 501 | 2024-09-23 15:47:22 | 23 | 9 | 2024 | 15 | 47 | 22 | 1 |

 

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.

 

| **id** | **invoice\_ts** |
| --- | --- |
| 501 | 2024-09-23 15:47:22 |
| 502 | 2024-09-23 15:47:22 |
| 503 | 2024-09-23 15:47:22 |
| 504 | 2024-09-23 15:47:22 |

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

 

| **Data Types** | **Descriptions** |
| --- | --- |
| timestamp | This is a timestamp without a time zone. It stores date and time values without any time zone information. |
| timestamptz | This is a timestamp with time zone. It stores both date and time values along with the time zone information. Internally, it's stored in Coordinated Universal Time (UTC) and converted to the specified time zone when displayed. |

 

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.

 

| **id** | **delivery\_ts** | **ny\_to\_local** |
| --- | --- | --- |
| 301 | 2024-09-23 15:47:22 | 2024-09-23 22:47:22+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*](https://www.sap-press.com/sql_6043/?utm_source=sappressblog&utm_medium=referral&utm_campaign=Blogs&utm_term=2679_chapter7&utm_content=2679) by [Kerem Koseoglu](https://www.linkedin.com/in/keremkoseoglu/). 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](https://learning.sap-press.com/abap), he is also proficient in database-driven development using [Python ](https://learning.rheinwerk-computing.com/python)and Swift. He is the author of [*Design Patterns in ABAP Objects*](https://www.sap-press.com/design-patterns-in-abap-objects_4277/?utm_source=sappressblog&utm_medium=referral&utm_campaign=Blogs&utm_term=2679_chapter7&utm_content=1464) (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.

[View full post](https://blog.rheinwerk-computing.com/temporal-functions-in-sql)

```json
{
  "@context" : "http://schema.org",
  "@type" : "BlogPosting",
  "author" : {
    "@type" : "Person",
    "name" : "Rheinwerk Computing"
  },
  "dateModified" : "2025-12-17T13:50:06.591Z",
  "datePublished" : "2025-05-14T13:00:00Z",
  "headline" : "Temporal Functions in SQL",
  "image" : {
    "@type" : "ImageObject",
    "height" : 443,
    "url" : "https://5707200.fs1.hubspotusercontent-na1.net/hubfs/5707200/Temporal%20Functions%20in%20SQL.jpg",
    "width" : 1440
  },
  "mainEntityOfPage" : "https://blog.rheinwerk-computing.com/temporal-functions-in-sql",
  "publisher" : {
    "@type" : "Organization",
    "logo" : {
      "@type" : "ImageObject",
      "height" : 0.0,
      "url" : "https://5707200.fs1.hubspotusercontent-na1.net/hubfs/5707200/publishing_computing_logo.svg",
      "width" : 0.0
    },
    "name" : "Blog"
  }
}
```