Converting between date, time, and text

Time Series Analysis in PostgreSQL

Jasmin Ludolf

Content Developer, DataCamp

Format strings

YYYY : four digit year
mm : two digit numerical month
DD : two digit numerical day
HH24 : two digit hour for a 24 hour clock
HH12 : two digit hour for a 12 hour clock
HH : two digit hour of day using 12 hour clock
MI : minute
SS : second

Convert strings into dates

TO_DATE() : converts strings into dates
TO_DATE('text string', 'format string')
The wrong format string may result in incorrect data or an error

SELECT
    TO_DATE('2023-01-15', 'YYYY-MM-DD') AS date_1,
    TO_DATE('20231501', 'YYYYDDMM') AS date_2,
    TO_DATE('Jan 15, 2023', 'Mon DD, YYYY') AS date_3;

|date_1    |date_2    |date_3    |
|----------|----------|----------|
|2023-01-15|2023-01-15|2023-01-15|

Convert strings into dates

CAST() : converts one data type into another
Use CAST() for any data type
CAST('string' AS data_type)

SELECT
    CAST('2023-01-15' AS DATE) AS date_1,
    CAST('20230115' AS DATE) AS date_2,
    CAST('Jan 15, 2023' AS DATE) AS date_3;

|date_1    |date_2    |date_3    |
|----------|----------|----------|
|2023-01-15|2023-01-15|2023-01-15|

The cast operator

:: the cast operator
Works the same way as CAST()
Only in PostgreSQL

SELECT
    '2023-01-15'::DATE AS date_1,
    '20230115'::DATE AS date_2,
    'Jan 15, 2023'::DATE AS date_3;

|date_1    |date_2    |date_3    |
|----------|----------|----------|
|2023-01-15|2023-01-15|2023-01-15|

Convert into dates and times

TO_TIMESTAMP(): converts strings into dates and times
TO_TIMESTAMP('string', 'format string')
Includes dates, times, and time zones

SELECT
    TO_TIMESTAMP('Jan 15, 2023 14:02:01', 'Mon DD, YYYY HH24:MI:SS') AS date_time;

|date_time                |
|-------------------------|
|2023-01-15 14:02:01+01:00|

Converting unix time

Unix time can be converted using TO_TIMESTAMP()

SELECT
     TO_TIMESTAMP(1483444800) AT TIME ZONE 'UTC' as datetime;

|datetime           |
|-------------------|
|2017-01-03 12:00:00|

Extracting unix time

EXTRACT(): retrieves values
With Unix time, it calculates the time elapsed from the epoch to the time stamp.

SELECT
    EXTRACT(epoch FROM TIMESTAMP '2017-01-03 12:00:00') AS unix_time;

|unix_time |
|----------|
|1483444800|

Converting fields

Uniform fields

-> use TO_DATE or TO_TIMESTAMP

date
Jan 15, 2023
Jan 16, 2023
Jan 17, 2023

SELECT TO_DATE(date, 'Mon DD, YYYY');

|date      |
|----------|
|2023-01-15|
|2023-01-16|
|2023-01-17|

Converting fields

Varied fields

-> use CAST() or ::

date
2023-01-15
20230116
Jan 17, 2023

SELECT CAST(date AS DATE);

|date      |
|----------|
|2023-01-15|
|2023-01-16|
|2023-01-17|

Convert date or time into text

TO_CHAR() : converts datetime data into text
TO_CHAR(date or time, 'format string')

SELECT
    timestamp_field,
    TO_CHAR(timestamp_field, 'YYYY-mm-DD HH12:MI:SS') AS timestamp_text
FROM timetable;

|timestamp_field    |timestamp_text     |
|-------------------|-------------------|
|2015-07-14 11:49:00|2015-07-14 11:49:00|
|2020-10-18 20:53:50|2020-10-18 08:53:50|
|2020-12-31 12:59:59|2020-12-31 12:59:59|

Verify the data types

SELECT
    pg_typeof(timestamp_field) AS "type of(timestamp_field)",
    pg_typeof(TO_CHAR(timestamp_field, 'YYYY-mm-DD HH12:MI:SS')) 
    AS "type of(timestamp_text)"
FROM timetable;

|type of(timestamp_field)   |type of(timestamp_text)|
|---------------------------|-----------------------|
|timestamp without time zone|text                   |

Different delimiters

SELECT
    TO_CHAR(timestamp_field, 'YYYY/mm/DD') AS slashes,
    TO_CHAR(timestamp_field, 'YYYY.mm.DD') AS dots,
    TO_CHAR(timestamp_field, '"Year": YYYY "Month": mm "Day": DD') AS labels
FROM timetable;

|slashes   |dots      |labels                      |
|----------|----------|----------------------------|
|2015/07/14|2015.07.14|Year: 2015 Month: 07 Day: 14|
|2020/10/18|2020.10.18|Year: 2020 Month: 10 Day: 18|
|2020/12/31|2020.12.31|Year: 2020 Month: 12 Day: 31|
|2021/01/01|2021.01.01|Year: 2021 Month: 01 Day: 01|

Non-numeric text

SELECT
    TO_CHAR(timestamp_field, 'Dy, Mon DD, YYYY') AS date
FROM timetable;

|date             |
|-----------------|
|Tue, Jul 14, 2015|
|Sun, Oct 18, 2020|
|Thu, Dec 31, 2020|
|Fri, Jan 01, 2021|

Custom formats

SELECT TO_CHAR(time_field, 'HH24:MI') AS "HH:MM"
FROM timetable;

|HH:MM|
|-----|
|11:49|
|20:53|
|12:59|
|00:01|

AM and PM

SELECT
    TO_CHAR(time_field, 'HH12:MI:SS AM') AS "AM",
    TO_CHAR(time_field, 'HH12:MI:SS pm') AS pm
FROM timetable;

|AM         |pm         |
|-----------|-----------|
|11:49:00 AM|11:49:00 am|
|08:53:50 PM|08:53:50 pm|
|12:59:59 PM|12:59:59 pm|
|12:01:02 AM|12:01:02 am|

MDY format

SELECT
    TO_CHAR(date_field, 'MM/DD/YYYY') AS "MDY Numeric",
    TO_CHAR(date_field, 'Mon DD, YYYY') AS "MDY Expanded",
    TO_CHAR(timetz_field, 'HH24:MI:SS TZ') AS upper_case,
    TO_CHAR(timetz_field, 'HH24:MI:SS tz') AS lower_case,
    TO_CHAR(timetz_field, 'HH24:MI:SS OF') AS utc_offset
FROM timetable;

|MDY Numeric|MDY Expanded|upper_case  |lower_case  |utc_offset  |
|-----------|------------|------------|------------|------------|
|12/31/2020 |Dec 31, 2020|18:49:00 UTC|18:49:00 utc|18:49:00 +00|

Let's practice!

Time Series Analysis in PostgreSQL