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

-- Data types for storing a date or a date/time value
DATE - format YYYY-MM-DD
TIME - format HH:MI:SS
TIMESTAMP - format: YYYY-MM-DD HH:MI:SS   (MYSQL equivalent is DATETIME)
INTERVAL - range

2.1. Casting a String as Date using CAST or :: (colons)

-- Converting to date
SELECT 
    CAST('01-01-2024' AS DATE),
    '01-01-2024'::date;
/*
date	      date
2024-01-01    2024-01-014
*/

2.2. Converting Date to String/Changing the Date Format

-- change date format
SELECT
    create_date,
    TO_CHAR(create_date,'YYYY-MM')
FROM customer;
/*
create_date	to_char
2/14/06    	2006-02
2/14/06	        2006-02
...
*/

TO_CHAR() is a powerful function that allows us to convert date/time datatypes into different string objects according to the given format. For full list of its capabilities visit: https://www.postgresql.org/docs/current/functions-formatting.html

The following queries enable us to obtain the 'day' information from a timestamp value, convert an interval data type into time string, and convert an integer value of 125 into text data type:

SELECT TO_CHAR('2015-10-06 17:31:14'::timestamp,'Day') 
-- Tuesday

SELECT TO_CHAR(INTERVAL '15h 2m 12s', 'HH24:MI:SS') 
-- 15:02:12

SELECT 125 as num, TRIM(TO_CHAR(125, '999')) as str
/*
num (integer)     str(text)
125	                "125"
*/

Note the use of TRIM function.TRIM has been used because the TO_CHAR function adds a preceding white space to value.

Below is the table including the functions are used to convert a specific date type to another the data type:

Function → Outcome

Description

Usage → Result

to_char( timestamp, text ) → text

Converts time stamp to string according to the given format.

to_char(timestamp '2002-04-20 17:31:12.66', 'HH12:MI:SS') → 5:31:12

to_char ( interval, text ) → text

Converts interval to string according to the given format.

to_char(interval '15h 2m 12s', 'HH24:MI:SS') → 15:02:12

to_char ( numeric_type, text ) → text

Converts number to string according to the given format; available for integer, bigint, numeric, real, double precision.

to_char(125, '999') → 125 to_char(125.8::real, '999D9') → 125.8 to_char(-125.8, '999D99S') → 125.80-

to_date ( text, text ) → date

Converts string to date according to the given format.

to_date('05 Dec 2000', 'DD Mon YYYY') → 2000-12-05

to_number ( text, text ) → numeric

Converts string to numeric according to the given format.

to_number('12,454.8-', '99G999D9S') → -12454.8

to_timestamp ( text, text ) → timestamp with time zone

Converts string to time stamp according to the given format.

to_timestamp('05 Dec 2000', 'DD Mon YYYY') → 2000-12-05 00:00:00-05

2.3. Extracting Part of Dates

2.3.1. EXTRACT ('field/precision' FROM date_source)

-- returns only the part specified, e.g. 2023
SELECT
    payment_date,
    EXTRACT('year' FROM payment_date) AS year, 
    EXTRACT('quarter' FROM payment_date) AS quarter, 
    EXTRACT('month' FROM payment_date) AS month, 
    EXTRACT('week' FROM payment_date) AS week, -- week number
    EXTRACT('day' FROM payment_date) AS day, 
    EXTRACT('hour' FROM payment_date) AS hour, 
    EXTRACT('minute' FROM payment_date) AS minute, 
    EXTRACT('second' FROM payment_date) AS second, 
    EXTRACT('decade' FROM payment_date) AS decade, 
    EXTRACT('dow' FROM payment_date) AS day_of_week
FROM customer;

/*
payment_date	                year	quarter	month	week	day	hour	minute	second	        decade	day_of_week
2007-02-15 22:25:46.996577	2007	1	2	7	15	22	25	46.996577	200	4
2007-02-16 17:23:14.996577	2007	1	2	7	16	17	23	14.996577	200	5
2007-02-16 22:41:45.996577	2007	1	2	7	16	22	41	45.996577	200	5
...
*/

2.3.2. DATE_PART('field/precision', date_source)

-- Similar output as EXTRACT
SELECT
    payment_date,
    DATE_PART('year',  payment_date) AS year, 
    DATE_PART('quarter',  payment_date) AS quarter, 
    DATE_PART('month',  payment_date) AS month, 
    DATE_PART('week',  payment_date) AS week, -- week number
    DATE_PART('day', payment_date) AS day, 
    DATE_PART('hour',  payment_date) AS hour, 
    DATE_PART('minute',  payment_date) AS minute, 
    DATE_PART('second',  payment_date) AS second, 
    DATE_PART('decade',  payment_date) AS decade, 
    DATE_PART('dow',  payment_date) AS day_of_week
FROM payment;
/*
payment_date	                year	quarter	month	week	day	hour	minute	second	        decade	day_of_week
2007-02-15 22:25:46.996577	2007	1	2	7	15	22	25	46.996577	200	4
2007-02-16 17:23:14.996577	2007	1	2	7	16	17	23	14.996577	200	5
2007-02-16 22:41:45.996577	2007	1	2	7	16	22	41	45.996577	200	5
...
*/

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(field FROM source)

EXTRACT uses keyword syntax and is SQL-standard compliant.
It strictly returns double precision values.

Example:

SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40');

DATE_PART:

DATE_PART('field', source)

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:

SELECT DATE_PART('year', TIMESTAMP '2001-02-16 20:38:40');

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.

As for dow, the week starts on Sunday (0) and ends on Saturday (6).

2.3.3. Truncating Dates: DATE_TRUNC('field/precision', date_source)

DATE_TRUNC function is used to truncate a date or timestamp to a specified precision. Essentially, it rounds down a timestamp or interval to the nearest specified frequency like year, month, week, day, hour, minute, or second.

SELECT DATE_TRUNC('month', timestamp '2023-03-15 12:05:55');
-- Result: 2023-03-01 00:00:00
"""
In the example above, 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.
"""

-- OR
SELECT
    payment_date,
    DATE_TRUNC('year', payment_date) AS year,
    DATE_TRUNC('quarter', payment_date) AS quarter,
    DATE_TRUNC('month', payment_date) AS month, 
    DATE_TRUNC('week', payment_date) AS week, -- date of the 1st day of the week (every Monday's date)
    DATE_TRUNC('day', payment_date) AS day, 
    DATE_TRUNC('hour', payment_date) AS hour, 
    DATE_TRUNC('minute', payment_date) AS minute, 
    DATE_TRUNC('second', payment_date) AS second, 
    DATE_TRUNC('decade', payment_date) AS decade
FROM payment;


/*
payment_date	                year	                quarter	                month	                week	                day	                hour	                minute	                second	                decade
2007-01-15 22:25:46.996577	2007-01-01 00:00:00	2007-01-01 00:00:00	2007-01-01 00:00:00	2007-01-15 00:00:00	2007-01-15 00:00:00	2007-01-15 22:00:00	2007-01-15 22:25:00	2007-01-15 22:25:46	2000-01-01 00:00:00
2007-02-15 22:25:46.996577	2007-01-01 00:00:00	2007-01-01 00:00:00	2007-02-01 00:00:00	2007-02-12 00:00:00	2007-02-15 00:00:00	2007-02-15 22:00:00	2007-02-15 22:25:00	2007-02-15 22:25:46	2000-01-01 00:00:00
...
*/

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

SELECT 
    NOW(),                             -- with time zone (cast it to timestamp to remove timezone)
    CURRENT_DATE AS date,
    CURRENT_TIME AS time,              -- with time zone
    CURRENT_TIMESTAMP AS timestamp,    -- with time zone
    LOCALTIME AS localtime,            -- without timezone
    LOCALTIMESTAMP AS localtimestamp   -- without time zone
/*
now	                        date	        time	                timestamp	                 localtime	    localtimestamp
2024-01-04 15:18:14.227658-05	2024-01-04	15:18:14.227658-05:00	2024-01-04 15:18:14.227658-05    15:18:14.227658    2024-01-04 15:18:14.227658
*/

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

2.5. Obtaining Current Date and/or Time in Different Time Zone

SELECT 
    CURRENT_TIME AS time, 
    CURRENT_TIME AT TIME ZONE 'NZT' AS time_NZT
/*
time                    time_NZT
15:29:46.973841-05:00   08:29:46.973841+12:00
*/

2.6. Converting Current Date/Time

-- to retrieve the date from NOW
SELECT NOW()::date, CAST (NOW() AS date) 
/*
now          now
2024-01-04   2024-01-04
*/


-- to retrieve the date from CURRENT_TIMESTAMP
SELECT CURRENT_TIMESTAMP::date
/*
current_timestamp
2024-01-04   
*/

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'

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

3.1. Adding/Subtracting Date/Time Data

SELECT NOW() + INTERVAL '1 day';  -- Adds 1 day to the current timestamp
SELECT CURRENT_DATE - 5;         -- Subtracts 5 days from the current date

SELECT 
    DATE '2024-01-04' + INTEGER '3' AS diff_days, -- same as DATE '2024-01-04' + 3
    DATE '2024-01-04' - DATE '2023-12-04' AS diff_days_2, 
    TIMESTAMP '2024-01-04 00:00:00' - TIMESTAMP '2024-01-02 12:00:00' AS diff_days_3;

/*
diff_days     diff_days_2    diff_days_3
(date)        (integer)      (inteval)
2024-01-07    31	     1 day 12:00:00
*/

3.2. Adding/Subtracting Date/Time Data using INTERVAL

SELECT
    DATE '2024-01-04' + INTERVAL '3' AS diff_seconds,
    DATE '2024-01-04' + INTERVAL '3 minute' AS diff_minutes,
    DATE '2024-01-04' + INTERVAL '3 hour' AS diff_hours,
    DATE '2024-01-04' + INTERVAL '3 day' AS diff_days_,
    DATE '2024-01-04' - 7 * INTERVAL '1 day' AS diff_days;

/*
-- All outputs are 'timestamp without time zone' type 
diff_seconds	        diff_minutes	        diff_hours	        diff_days_	        diff_days
2024-01-04 00:00:03	2024-01-04 00:03:00	2024-01-04 03:00:00	2024-01-07 00:00:00	2023-12-28 00:00:00
*/

-- another example
SELECT '1 hour'::interval * 3;   -- Multiplies an interval of 1 hour by 3

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:

SELECT DATE '2024-01-04' + INTERVAL '3'; --> 2024-01-04 00:00:03
SELECT DATE '2024-01-04' + INTERVAL '3 day'; --> 2024-01-07 00:00:00

To calculate the date and time of the expected return date of dvds with 4 days and 12 hours rental period:

SELECT 
    rental_id,
    customer_id,
    rental_date + INTERVAL '4 days 12 hours' as expected_return
FROM rental; 
/*
rental_id	customer_id	rental_date            expected_return
2	        459	        2005-05-24 22:54:33    2005-05-29 10:54:33
3	        408	        2005-05-24 23:03:39    2005-05-29 11:03:39
4	        333	        2005-05-24 23:04:41    2005-05-29 11:04:41
...
*/

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

SELECT 
-- AGE(timestamp 'ending_point', timestamp 'starting_point')
   AGE(timestamp '2024-01-04', timestamp '2023-01-01') AS age_diff; --> 1 year 3 days

When only one argument is provided, AGE() subtracts from the current date:

SELECT AGE(timestamp '2023-01-01') AS age_from_now; --> 1 year 3 days
-- the same as AGE(NOW(), timestamp '2023-01-01')

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.

SELECT
    CURRENT_TIMESTAMP - timestamp '2020-01-21 00:05:30' --> 1443 days 23:54:30.4732580
    now()::timestamp - timestamp '2020-01-21 00:05:30'  --> 1443 days 23:54:30.4732580
    AGE(NOW(), timestamp '2020-01-21 00:05:30')         --> 4 years 3 days 23:54:30.473258
    AGE(timestamp '2020-01-01 00:05:30'),               --> 4 years 3 days 23:54:30
    AGE(date '2024-01-04', date '2020-01-01');          --> 4 years 3 days

3.4. Converting Integers to Time

Occasionally, we might need to convert integers to time objects. The following code converts the integer 10 to '00:00:10':

SELECT 
    TO_CHAR(
    '00:00:00'::TIME + MAKE_INTERVAL(secs => 10), 'HH24:MI:SS'
    ) AS formatted_time     --> 00:00:10

The use of TO_CHAR function is optional

Last updated 5 months ago