Date/Time Operations
1. Dataset
In this section, we will be using customer,
payment
and rental
tables from the dvdrental
database ( formally known as Sakila Database) which can be accessed via: https://www.postgresqltutorial.com/postgresql-getting-started/postgresql-sample-database/.
If you like to download and the run examples in your local postgres server, you can follow the instructions here.
Recall Date/Time Data Types:
DATE
(stores date data. ISO 3601 format standard: 'yyyy-mm-dd'; e.g. '2024-01-01'),TIME
(stores time data with microsecond precision with or without time zone, ex '03:15:33.467'),TIMESTAMP
(stores date and time data with or without timezone. e.g. '2023-01-21 02:06:11.86123+00' )INTERVAL
(stores date and time data as a period of time in years, months, days, hours, seconds, etc. e.g. '7 days')
The SQL standard requires that writing just timestamp
be equivalent to timestamp without time zone
, and PostgreSQL honors that behavior. timestamptz
is accepted as an abbreviation for timestamp with time zone
; this is a PostgreSQL extension.
2. Date Operations
A. Casting a String as Date: CAST or :: (colons)
B. Converting Date to String/Changing the Date Format: TO_CHAR
C. Extracting Part of Dates: EXTRACT, DATE_PART, DATE_TRUNC
i. EXTRACT ('field' FROM date_source)
ii. DATE_PART('field', date_source)
EXTRACT vs. DATE_PART Functions in PostgreSQL
Both EXTRACT
and DATE_PART
are functions used to retrieve subfields such as year or hour from date/time values in PostgreSQL. However, there are slight differences in their usage and behavior.
EXTRACT:
EXTRACT
uses keyword syntax and is SQL-standard compliant.It strictly returns double precision values.
Example:
DATE_PART:
DATE_PART
is a more traditional function with arguments enclosed in parentheses.It can return either double precision or integer values depending on the context.
Example:
Both functions will give you the same result when used to extract the same part of a date/time value, but EXTRACT
is the preferred function when adhering to SQL standards.
D. Truncating Dates: DATE_TRUNC
DATE_TRUNC
function is used to truncate a date or timestamp to a specified precision ( The part of the timestamp (e.g., year, month, day) to which you want to truncate). Essentially, it rounds down a timestamp or interval to the nearest specified frequency, such as year, month, week, day, hour, minute, or second.
Syntax: DATE_TRUNC('precision', date_column)
In this example, the timestamp '2023-03-15 12:05:55'
is truncated to the first moment of March 2023, so the time part is reset to midnight (00:00:00
) on the first day of the month.
E. Retrieving Current Date and/or Time (Local):
NOW - returns the current date and time as a timestamp with the time zone, based on the system clock of the PostgreSQL server instance. The precision of the returned value includes microseconds.
CURRENT_DATE - returns the current date at the start of the current transaction; the time component is omitted, and it doesn't have a time zone.
CURRENT_TIME - returns the current time at the start of the current transaction. It includes microseconds and a time zone.
CURRENT_TIMESTAMP - functions similarly to
NOW()
but is often preferred in SQL-standard-sensitive environments. This function returns the current date and time as a timestamp with time zone.LOCALTIME - returns the current time without a time zone.
LOCALTIMESTAMP - returns a timestamp without a time zone.
Note that you may get a result in different time zones, If you run a current time function against a connected database.
Here is the list of all timezone
F. Obtaining Current Date and/or Time in Different Time Zone
G. Converting Current Date/Time
3. Date/Time Arithmetic
In PostgreSQL, we can perform various date and time arithmetic operations, such as adding or subtracting intervals from dates and timestamps to calculate new timestamps. We can also subtract one date from another to get the number of elapsed days, or perform operations like negating intervals and multiplying or dividing intervals by scalars to adjust their magnitudes. These operations allow for flexible date-time calculations essential for handling temporal data. Here Is the summary of calculations along with their corresponding results:
Date/Time Arithmetic | Description | Query | Result(s) |
---|---|---|---|
date + integer → date | Add a number of days to a date | SELECT date '2001-09-28' + 7 | 2001-10-05 |
date + interval → timestamp | Add an interval to a date | SELECT date '2001-09-28' + interval '1 hour' | 2001-09-28 1:00:00 |
date + time → timestamp | Add a time-of-day to a date | SELECT date '2001-09-28' + time '03:00' | 2001-09-28 3:00:00 |
interval + interval → interval | Add intervals | SELECT interval '1 day' + interval '1 hour' | 1 day 01:00:00 |
timestamp + interval → timestamp | Add an interval to a timestamp | SELECT timestamp '2001-09-28 01:00' + interval '23 hours' | 2001-09-29 0:00:00 |
time + interval → time | Add an interval to a time | SELECT time '01:00' + interval '3 hours' | 4:00:00 |
- interval → interval | Negate an interval | SELECT - interval '23 hours' | -23:00:00 |
date - date → integer | Subtract dates, producing the number of days elapsed | SELECT date '2001-10-01' - date '2001-09-28' | 3 |
date - integer → date | Subtract a number of days from a date | SELECT date '2001-10-01' - 7 | 2001-09-24 |
date - interval → timestamp | Subtract an interval from a date | SELECT date '2001-09-28' - interval '1 hour' | 2001-09-27 23:00:00 |
time - time → interval | Subtract times | SELECT time '05:00' - time '03:00' | 2:00:00 |
time - interval → time | Subtract an interval from a time | SELECT time '05:00' - interval '2 hours' | 3:00:00 |
timestamp - interval → timestamp | Subtract an interval from a timestamp | SELECT timestamp '2001-09-28 23:00' - interval '23 hours' | 2001-09-28 0:00:00 |
interval - interval → interval | Subtract intervals | SELECT interval '1 day' - interval '1 hour' | 1 day -01:00:00 |
timestamp - timestamp → interval | Subtract timestamps (converting 24-hour intervals into days, similarly to justify_hours()) | SELECT timestamp '2001-09-29 03:00' - timestamp '2001-07-27 12:00' | 63 days 15:00:00 |
interval * double precision → interval | Multiply an interval by a scalar | SELECT interval '1 second' * 900 SELECT interval '1 day' * 21 SELECT interval '1 hour' * 3.5 | 00:15:00 21 days 03:30:00 |
interval / double precision → interval | Divide an interval by a scalar | SELECT interval '1 hour' / 1.5 | 0:40:00 |
A. Adding/Subtracting Date/Time Data
B. Adding/Subtracting Date/Time Data using INTERVAL
Note that the default precision is 'seconds' when we perform addition/subtraction with INTERVAL. Therefore if we would like to get the difference in days, we then define it in the precision:
To calculate the date and time of the expected return date of dvds with 4 days and 12 hours rental period:
C. Calculating Date/Time Differences with AGE()
The AGE()
function is used to calculate the age based on the current date or between two specified dates. It subtracts the second argument (starting point) from the first argument (ending point) to produce an interval
representing years, months, and days.
When only one argument is provided, AGE()
subtracts from the current date:
The result of the AGE()
function provides a convenient way to display the age in years, months, and days without having to perform complex interval arithmetic.
Last updated