SQL Date and Time Functions and Data Types

SQL Tutorial Home

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 and starting_freeze. As both are timestamp values, the result is an interval data type value representing the elapsed time between starting_freeze and delivery_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 180 day
            when apple_variety <> ‘Red Delicious’ then 
              INTERVAL 240 day
        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 180 day .

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!

IN THIS PAGE