Amazon Redshift Date Format Conversion and Examples

Date data type is one of the complicated type is database. Date data types are used to store the date and time fraction values. Amazon Redshift Date format includes four data types, and are used to store the date with time details:

DATE: for year, month, day storage.
TIME: for hour, minute, second, fraction with (includes 6 decimal positions).
TIMESTAMPTZ: same as TIME, also includes time zone information.
TIMESTAMP: for year, month, day, hour, minute, second, fraction (includes 6 decimal positions).

Amazon Redshift Date Format Conversion

Redshift can convert quoted date strings values to datetime data type. Amazon Redshift accepts different date formats, including ISO date time strings. You can directly convert the format by specifying the cast operator (‘::’) such as ::date, ::time, ::timestamp after the date string literal. You can use the Redshift date format functions to convert the string literals, integer, date/time etc to required format.

Read:

Convert a String to Date format using TO_DATE() Function

Below is the example to use Redshift TO_DATE function to convert string having date to date format:

training=# Select TO_DATE( '2010-12-23 23:45:58','YYYY-MM-DD HH24:MI:SS'); 
 to_date 
------------ 
 2010-12-23 
(1 row)

Convert a String to timestamp format using TO_TIMESTAMP() Function

Below is the example to use Redshift TO_TIMESTAMP function to convert string having date time to Redshift timestamp format:

training=# Select to_timestamp('05 Dec 2017', 'DD Mon YYYY'); 
 to_timestamp 
------------------------ 
 2017-12-05 00:00:00+00 
(1 row)

Redshift Date Format Conversion Examples

Convert Redshift timestamp to YYYYMMDD format: Below example demonstrate conversion of date value to ‘YYYYMMDD’ format using to_char and to_date function:

training=# select TO_CHAR(TO_DATE( '2016-10-22 22:30:58','YYYY-MM-DD HH24:MI:SS'),'YYYYMMDD'); 
 to_char 
---------- 
 20161022 
(1 row)

Redshift Convert YYYYMMDD to date format:

Below example demonstrate conversion of date value from ‘YYYYMMDD’ format to date format:

training=# select date(to_char(20161022,'99999999')); 
 date 
------------ 
 2016-10-22 
(1 row)

Convert Redshift timezone value to timestamp format:

If you have timezone values, you can use TIMEZONE function to convert timezone value to timestamp. Below example demonstrates usage of TIMEZONE function:

 training=# select TIMEZONE ('GMT',now() ); 
 timezone 
---------------------------- 
 2017-12-15 12:39:46.198881 
(1 row

Using Redshift DATE and DATETIME variables in SQL Comparison Statements

The type of the SQL comparison parameter must match the type of the column in the database table.

For examples, you cannot use a DATE variable when the target column is a DATETIME. You must explicitly type cast the values to perform the comparison. You can also use ::date, ::time, ::timestamp after values value to convert it to proper format to compare with other column.

For example,

select CASE WHEN col1::date = col2 then “Mathing” ELSE “Not Matching” END FROM somedbtable; 

training=# select '2017-12-15 12:38:45.262702'::timestamp;
 timestamp 
---------------------------- 
 2017-12-15 12:38:45.262702 
(1 row)

training=# select '2017-12-15'::date; 
 date 
------------ 
 2017-12-15 
(1 row)

training=# select '12:38:45.262702'::time; 
 time 
----------------- 
 12:38:45.262702 
(1 row)