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:

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:

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:

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:

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:

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:

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!