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')
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"
*/Below is the table including the functions are used to convert a specific date type to another the data type:
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)EXTRACTuses 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_PARTis 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.
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 toNOW()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
*/
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 + 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
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 daysWhen 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:10The use of TO_CHAR function is optional
Last updated
Was this helpful?