Konwersja między datą, czasem i tekstem

Analiza szeregów czasowych w PostgreSQL

Jasmin Ludolf

Content Developer, DataCamp

Ciągi formatujące

YYYY : czterocyfrowy rok
MM : dwucyfrowy miesiąc
DD : dwucyfrowy dzień
HH24 : dwucyfrowa godzina w formacie 24-godzinnym
HH12 : dwucyfrowa godzina w formacie 12-godzinnym
HH : dwucyfrowa godzina dnia w formacie 12-godzinnym
MI : minuta
SS : sekunda

Konwersja ciągów znaków na daty

TO_DATE() : konwertuje ciągi znaków na daty
TO_DATE('text string', 'format string')
Nieprawidłowy ciąg formatujący może spowodować błędne dane lub błąd

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|

Konwersja ciągów znaków na daty

CAST() : konwertuje jeden typ danych na inny
Używaj CAST() dla dowolnego typu danych
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|

Operator rzutowania

:: operator rzutowania
Działa tak samo jak CAST()
Tylko w 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|

Konwersja na daty i czasy

TO_TIMESTAMP(): konwertuje ciągi znaków na daty i czasy
TO_TIMESTAMP('string', 'format string')
Obsługuje daty, czasy i strefy czasowe

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|

Konwersja czasu uniksowego

Czas uniksowy można konwertować za pomocą TO_TIMESTAMP()

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

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

Wyodrębnianie czasu uniksowego

EXTRACT(): pobiera wartości
Dla czasu uniksowego oblicza czas, który upłynął od epoki do znacznika czasu.

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

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

Konwersja pól

Pola jednolite

-> użyj TO_DATE lub 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|

Konwersja pól

Pola niejednolite

-> użyj CAST() lub ::

date
2023-01-15
20230116
Jan 17, 2023

SELECT CAST(date AS DATE);

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

Konwersja daty lub czasu na tekst

TO_CHAR() : konwertuje dane daty/czasu na tekst
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|

Weryfikacja typów danych

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                   |

Różne separatory

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|

Tekst nienumeryczny

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|

Formaty niestandardowe

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

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

AM i 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|

Format MDY

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|

Czas na ćwiczenia!

Analiza szeregów czasowych w PostgreSQL