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'	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

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