SQL Date and Time Data Types and Functions
SQL databases provide data types related to date and time data, as you learned in the SQL Data Types lesson. Now you can see the details of how these data types are related, how to create expressions involving different date data types and use some SQL date and time functions.
Here is the sample table used in the examples. Building on the apple example, a new table apple_sells
is added to the apple database. The apple_sells
table stores one record per each apple sales transaction.
The schema of the table is:
Customer Name | Apple Variety | Date of Sell | Tons Sold | Ton Price | Delivery date | Starting Freeze |
---|---|---|---|---|---|---|
SMart | Red Delicious | 2020-02-23 | 60 | 54.56 | 2020-02-28 | 2020-01-08 10:00 |
JB | Red Globus | 2020-03-02 | 35 | 61.50 | 2020-03-12 | 2020-01-15 09:30 |
JB | Magic Green | 2020-04-13 | 15 | 58.50 | 2020-04-22 | 2020-01-07 08:23 |
SMart | Red Globus | 2020-05-21 | 100 | 61.00 | 2020-05-28 | 2020-01-15 09:30 |
SMart | Red Delicious | 2020-05-21 | 90 | 54.56 | 2020-05-30 | 2020-01-08 10:00 |
The starting_freeze
column tracks when the apples entered into the refrigerator rooms. This information is provided to customers to meet food regulations.
Date Arithmetic
The SQL Data Types related to date and time are DATE
, TIMESTAMP
, TIME
and INTERVAL
. You can construct arithmetic operations or expressions involving different data types. Here are a few of the frequently used expressions:
Arithmetic operation | Result data type |
---|---|
TIMESTAMP + INTERVAL | TIMESTAMP |
TIMESTAMP - TIMESTAMP | INTERVAL |
INTERVAL + INTERVAL | INTERVAL |
INTERVAL * INTEGER | INTERVAL |
TIMESTAMP - INTERVAL | TIMESTAMP |
CAST(DATE, TIMESTAMP) + INTERVAL | TIMESTAMP |
CAST(DATE, TIMESTAMP) - TIMESTAMP | INTERVAL |
Suppose you need to inform the customers how long the apples have been in the refrigerator room:
SELECT customer_name,
apple_variety,
delivery_date,
CAST(delivery_date AS TIMESTAMP) - starting_freeze AS freeze_time
FROM apple_sells
To calculate the freeze_time
value:
- Convert
delivery_date
value from date to timestamp. This conversion is done by adding the time 00:00:00 to the date value. - Calculate the difference between
delivery_date
andstarting_freeze
. As both are timestamp values, the result is an interval data type value representing the elapsed time betweenstarting_freeze
anddelivery_date
.
Here is the result:
Customer Name | Apple Variety | Delivery Date | Freeze Time |
---|---|---|---|
SMart | Red Delicious | 2020-02-28 | 50 days 14:00:00 |
JB | Red Globus | 2020-03-12 | 56 days 14:30:00 |
JB | Magic Green | 2020-04-22 | 105 days 15:37:00 |
SMart | Red Globus | 2020-05-28 | 133 days 14:30:00 |
SMart | Red Delicious | 2020-05-30 | 142 days 14:00:00 |
State regulations for apples say Red Delicious must be consumed no later than 180 days since the starting freeze date and the other varieties at 240 days. To inform your customers of the deadline, you calculate the consume_deadline:
SELECT customer_name,
apple_variety,
delivery_date,
CAST(delivery_date, TIMESTAMP) - starting_freeze AS
time_stored_at_delivery,
starting_freeze +
case
when apple_variety = ‘Red Delicious’ then
INTERVAL ‘1 day’ * 180
when apple_variety <> ‘Red Delicious’ then
INTERVAL ‘1 day’ * 240
end AS deadline_to_consume
FROM apple_sells
The CASE
statement handles the different apple varieties. The 180 days is set with the expression: INTERVAL ‘1 day‘ * 180
.
Here are the results:
customer_name | apple_variety | delivery_date | time_stored_at_delivery | deadline_to_consume |
---|---|---|---|---|
SMart | Red Delicious | 2020-02-28 | 50 days 14:00:00 | 2020-07-06 10:00:00 |
JB | Red Globus | 2020-03-12 | 56 days 14:30:00 | 2020-09-11 09:30:00 |
JB | Magic Green | 2020-04-22 | 105 days 15:37:00 | 2020-09-03 08:23:00 |
SMart | Red Globus | 2020-05-28 | 133 days 14:30:00 | 2020-09-11 09:30:00 |
SMart | Red Delicious | 2020-05-30 | 142 days 14:00:00 | 2020-07-06 10:00:00 |
Functions to Manipulate SQL Date and Time Data Type
There are several SQL date and tIme functions to extract, manipulate and transform date and time data types. These examples cover CURRENT_DATETIME()
, the function EXTRACT()
and the SQL dialect specific TO_CHAR()
function.
CURRENT_DATETIME()
returns the current date and time as a TIMESTAMP
data type. Simply put it in your SQL where you need the current date and time.
The function EXTRACT()
enables you to extract an element from a date or timestamp value. For example, you can extract the year from a date value, or the hour from a timestamp value. Here is EXTRACT()
function syntax:
EXTRACT (FIELD FROM SOURCE)
Suppose you want to extract the month and day from the starting_freeze time for the year 2018:
SELECT ‘on ‘starting_freeze,
EXTRACT(MONTH FROM starting_freeze) AS month_freeze,
EXTRACT(DAY FROM starting_freeze) AS day_freeze
FROM apple_sells
WHERE EXTRACT(YEAR FROM starting_freeze ) = 2018
Here are the results:
starting_freeze | month_freeze | day_freeze |
---|---|---|
2020-01-08 10:00:00 | 1 | 8 |
2020-01-15 09:30:00 | 1 | 15 |
2020-01-07 08:23:00 | 1 | 7 |
2020-01-15 09:30:00 | 1 | 15 |
2020-01-08 10:00:00 | 1 | 8 |
Other date functions are oriented to date formatting. One popular example is the TO_CHAR()
function. This is a SQL dialect specific function, but is provided by all databases.
Here is an example of the Postgresql TO_CHAR()
function:
TO_CHAR ( EXPRESSION , FORMAT)
The expression can be a date, timestamp, or interval that is converted to a string according to the specified format. You want to show starting_freeze in a more readable format: Refrigerated at: 09:30:00 on Jan-15-2020.
Here is the query:
SELECT ‘Refrigerated at ‘||
TO_CHAR( starting_freeze, ‘HH24:MI:SS’)||
‘ on ‘||
TO_CHAR( starting_freeze, ‘Mon-DD-YYYY’) AS freezing_date
FROM apple_sells
Here are the results:
Freezing Date |
---|
Refrigerated at 10:00:00 on Jan-08-2020 |
Refrigerated at 09:30:00 on Jan-15-2020 |
Refrigerated at 08:23:00 on Jan-07-2020 |
Refrigerated at 09:30:00 on Jan-15-2020 |
Refrigerated at 10:00:00 on Jan-08-2020 |
Closing Words
In this lesson we learned several date expressions, functions, and interesting date arithmetic expressions where the result data type is different from the operators data types. Another function manipulated and formatted the date and timestamp values. In the next lesson covers interesting string related functions. Keep going, learn SQL
and increase your skills!