# 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](https://github.com/jOOQ/sakila)) which can be accessed via: . If you like to download and the run examples in your local postgres server, you can follow the instructions [here](https://www.postgresqltutorial.com/postgresql-getting-started/load-postgresql-sample-database/). **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') {% hint style="info" %} 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. {% endhint %} ## 2. Date Operations ```sql -- 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) ```sql -- 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 ```sql -- 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: 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: ```sql 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" */ ``` {% hint style="info" %} Note the use of `TRIM` function.`TRIM` has been used because the `TO_CHAR` function adds a preceding white space to value. {% endhint %} Below is the table including the functions are used to convert a specific date type to another the data type:
Function → OutcomeDescriptionUsage → Result
to_char( timestamp, text ) → textConverts 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 ) → textConverts 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 ) → textConverts 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 ) → dateConverts string to date according to the given format.to_date('05 Dec 2000', 'DD Mon YYYY') → 2000-12-05
to_number ( text, text ) → numericConverts string to numeric according to the given format.to_number('12,454.8-', '99G999D9S') → -12454.8
to_timestamp ( text, text ) → timestamp with time zoneConverts 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) ```sql -- 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) ```sql -- 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 ... */ ``` {% hint style="info" %} #### 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:** ```sql EXTRACT(field FROM source) ``` * `EXTRACT` uses keyword syntax and is SQL-standard compliant. * It strictly returns double precision values. Example: ```sql SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40'); ``` **DATE\_PART:** ```sql 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: ```sql 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. {% endhint %} {% hint style="info" %} As for **dow,** the week starts on Sunday (`0`) and ends on Saturday (`6`). {% endhint %} #### 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. ```sql 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 */ ``` {% hint style="info" %} 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`](https://www.postgresql.org/docs/7.2/timezones.html) {% endhint %} ### 2.5. Obtaining Current Date and/or Time in Different Time Zone ```sql 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 ```sql -- 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 ArithmeticDescriptionQueryResult(s)
date + integer → dateAdd a number of days to a dateSELECT date '2001-09-28' + 72001-10-05
date + interval → timestampAdd an interval to a dateSELECT date '2001-09-28' + interval '1 hour' 2001-09-28 1:00:00
date + time → timestampAdd a time-of-day to a dateSELECT date '2001-09-28' + time '03:00' 2001-09-28 3:00:00
interval + interval → intervalAdd intervalsSELECT interval '1 day' + interval '1 hour'1 day 01:00:00
timestamp + interval → timestampAdd an interval to a timestampSELECT timestamp '2001-09-28 01:00' + interval '23 hours' 2001-09-29 0:00:00
time + interval → timeAdd an interval to a timeSELECT time '01:00' + interval '3 hours'4:00:00
- interval → intervalNegate an intervalSELECT - interval '23 hours'-23:00:00
date - date → integerSubtract dates, producing the number of days elapsedSELECT date '2001-10-01' - date '2001-09-28'3
date - integer → dateSubtract a number of days from a dateSELECT date '2001-10-01' - 72001-09-24
date - interval → timestampSubtract an interval from a dateSELECT date '2001-09-28' - interval '1 hour'2001-09-27 23:00:00
time - time → intervalSubtract timesSELECT time '05:00' - time '03:00'2:00:00
time - interval → timeSubtract an interval from a timeSELECT time '05:00' - interval '2 hours'3:00:00
timestamp - interval → timestampSubtract an interval from a timestampSELECT timestamp '2001-09-28 23:00' - interval '23 hours'2001-09-28 0:00:00
interval - interval → intervalSubtract intervalsSELECT interval '1 day' - interval '1 hour'1 day -01:00:00
timestamp - timestamp → intervalSubtract 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 → intervalMultiply an interval by a scalarSELECT 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 → intervalDivide an interval by a scalarSELECT interval '1 hour' / 1.50:40:00
### 3.1. Adding/Subtracting Date/Time Data ```sql 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 ```sql 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 ``` {% hint style="info" %} 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: ```sql 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 ``` {% endhint %} To calculate the date and time of the expected return date of dvds with 4 days and 12 hours rental period: ```sql 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.** ```sql 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: ```sql 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. ```sql 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': ```sql 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