Postgres posiada cały zestaw typów do pracy z datami:

• timestamp without time zone (równoważny typowi timestamp; bez strefy czasowej) oraz timestamp with time zone
• date
• time without time zone (równoważny typowi time bez strefy czasowej) oraz time with timezone
• interval

Typy

Wartości tych typów:

• zajmują na nośniku różną ilość miejsca
• mają różne zakresy (typ date ma rozdzielczość jednego dnia, a pozostałe - jednej mikrosekundy)
• mają też oczywiście inne znaczenie.

Listę typow w postgresie przedstawia poniższa tabelka:

Opcjonalny parametr (p) może przyjąć wartość od 0 do 6 i oznacza liczbę cyfr dziesiętnych w polu sekund (domyślnie nie jest nałożone żadne ograniczenie).

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

postgres=# select time  '11:12:23.1234567';
      time       
-----------------
 11:12:23.123457
(1 row)

postgres=# select time (6) '11:12:23.1234567';
      time       
-----------------
 11:12:23.123457
(1 row)

postgres=# select time (2) '11:12:23.1234567';
    time     
-------------
 11:12:23.12
(1 row)

postgres=# select time (7) '11:12:23.1234567';
WARNING:  TIME(7) precision reduced to maximum allowed, 6
LINE 1: select time (7) '11:12:23.1234567';
               ^
      time       
-----------------
 11:12:23.123457
(1 row)

Typ interval może przyjąć parametr fields i może to być jedna z poniższych wartości:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

YEAR
MONTH
DAY
HOUR
MINUTE
SECOND
YEAR TO MONTH
DAY TO HOUR
DAY TO MINUTE
DAY TO SECOND
HOUR TO MINUTE
HOUR TO SECOND
MINUTE TO SECOND

Na przykład różnica czasu między dwiema datami wyrażona jest wartością typu interval:

1
2
3
4
5

postgres=# select timestamp '2021-12-24 19:00:00' - now() as "time to Cristmas Eve";
  time to Cristmas Eve   
-------------------------
 49 days 00:38:57.875692
(1 row)

Zastosowania

• do reprezentowania dat najlepiej użyć typu date
• do prezyzyjnego określenia czasu - jednego z wariantów typu time
• do określenia momentu zajścia zdarzenia tradycyjnie używa się wariantu timestamp
• natomiast w celu zapisania czasu trwania (bez konkretnych dat/czasów początku i końca) - typu interval

Wejście

Wprowadzanie dat

PostgreSQL jest bardzo liberalny jeśli chodzi o akceptowanie danych dat/czasu na “wejściu”. Akceptuje:

• daty w standardie ISO 8601
• daty w formatach YMD (year, month, day) i MDY (month, day, year), a także
• daty w kalendarzu juliańskim

W ogólności literały “czasowe” mają postać:

typ [(p)] ‘wartość’

a więc wartość literału jest zapisywana w pojedynczym apostrofie; na przykład:

1
2
3
4

date '2021-11-04'
date 'October 4, 2021'
date '20211104'
date '11/04/2021'

Wprowadzanie czasu

Typy czasowe mają opcjonalnie dodaną strefę czasową, która może być wyrażona jako przesunięcie względem UTC, jako skrót oraz jako pełna nazwa strefy:

1
2
3

time with time zone '04:05:06-08:00'
time with time zone '04:05:06 PST'
time with time zone '2003-04-12 04:05:06 America/New_York'

Wprowadzanie znaczników czasu

Literał warości timestamp ma postać napisu z datą, czasem, opcjonalnym przesunięciem oraz opcjonalnym znacznikiem AD lub BC. Należy użyć + lub - do oddzielenia timstampa i strefy, np.:

1
2

timestamp with time zone '2004-10-19 10:23:54+02'
timestamp with time zone '2004-10-19 10:23:54-02'

PostgreSQL określa typ na podstawie zadeklarowanego typu i nie parsuje w tym celu literału. Dlatego zapis:

1

timestamp '2004-10-19 10:23:54-02'

zostanie uznany za timestamp without time zone.

Specjalne literały czasu

Jako literału czasu można również użyć (w pojedynczych apostrofach) następujących wartości:

Wprowadzanie interwałów

Interwały mogą być wprowadzane na dwa sposoby:

1. W postaci niestandardowej mają postać

[@] quantity unit [quantity unit…] [direction] gdzie

• opcjonalny znak @ nic nie oznacza
• quantity to liczba ze znakiem
• unit to jedna z podanych wartości (w liczbie pojedynczej lub mnogiej w jęz. ang.): microsecond, millisecond, second, minute, hour, day, week, month, year, decade, century, millennium

przy czym dni, godziny, minuty i sekundy mogą być określone bez znaczników jednostek, np.

• ‘200-10’ to to samo co ‘200 years 10 months’
• ‘1 12:59:10’ to to samo, co ‘1 day 12 hours 59 min 10 sec’

2. W wersji standardowej (ISO 8601) mają postać:

P quantity unit [ quantity unit …] [ T [ quantity unit …]]

lub alternatywnie:

P [ years-months-days ] [ T hours:minutes:seconds ]

przy czym początkowe P jest obowiązkowe, a części mniejsze od dnia muszą pojawić się po (wymaganym wówczas) T.

Na przykład:

• P1Y2M3DT4H5M6S
• P0001-02-03T04:05:06

Znaczniki jednostek możliwe do użycia (ISO 8601):
Y 	Years
M 	Months (in the date part)
W 	Weeks
D 	Days
H 	Hours
M 	Minutes (in the time part)
S 	Seconds

Wyjście - wypisywanie dat

Daty/czas wg standardu SQL muszą być wypisywane w formacie ISO 8601. PostgreSQL czyni jedno odstępstwo od standardu: zamiast litery T oddzielającej datę od czasu używa on (zgodnie z zaleceniem RFC3339 spacji. Poza tym dopuszcza również inne sposoby wypisywania:

• ISO (ISO 8601) SQL standard, np. 1997-12-17 07:37:16-08
• SQL (tradycyjny), np. 12/17/1997 07:37:16.00 PST
• Postgres (oryginalny), np. Wed Dec 17 07:37:16 1997 PST
• German (regionalny), np. 17.12.1997 07:37:16.00 PST

Do bardziej precyzyjnego formatowania czasu służą funkcje formatujące, do których należą m.in.:

• to_char ( timestamp, text ) → text - konwertuje timestamp/czas do napisu wg podanego formatu:
  • to_char(timestamp ‘2002-04-20 17:31:12.66’, ‘HH12:MI:SS’) → 05:31:12
• to_date ( text, text ) → date - konwertuje/parsuje napis do daty wg podanegp formatu, np.
  • to_date(‘05 Dec 2000’, ‘DD Mon YYYY’) → 2000-12-05

Wzorce służące do budowania formatów można znaleźć w dokumentacji

Strefy czasowe

Życie na prawie kulistej Ziemi, na której doba trwa około 24 godziny, podzielonej na państwa, którym czasami udaje się dogadać w sprawie posługiwania się wspólnym standardem czasu… jest, cóż, skomplikowane. Zobaczmy, na czym polegają komplikacje.

Strefy czasowe zależą nie tylko od kształtu Ziemi, lecz także od decyzji politycznych. Na początku 19. wieku udało się wiele aspektów ustandaryzować, ale wprowadzenie okresów DST (daylight saving time - “oszczędzania dzięki światłu słonecznemu”) ponownie skomplikowało sposoby obliczania czasu.

Reguły określania stref

• Dla dat w przeszłości PostgreSQL używa reguł określania stref z bazy danych IANA (Olson)
• Dla dat w przyszłości ekstrapoluje w nieskończoność istniejące obecnie reguły (czyli zupełnie nierealistycznie zakłada, że nie zmienią się one w ogóle).

Sposoby określania strefy

Istnieją trzy sposoby określania strefy czasowej:

• pełna nazwa strefy (występująca w tabeli pg_timezone_names), np. Europe/Warsaw - nazwa ta pochodzi z bazy danych IANA i jest szeroko używana i rozpoznawana przez różne systemy informatyczne (patrz tabela pg_timezone_names)
• skrót nazwy strefy, np. CET; ta specyfikacja oznacza (i określa) jedynie bezwzględne przesunięcie względem strefy UTC (podczas gdy pełna nazwa strefy czasowej może oznaczać i zwykle oznacza również przesunięcie wynikające z przyjętego okresu DST) (patrz tabela pg_timezone_abbrevs)
• nazwy stref zgodne ze standardem POSIX Patrz Tabele

Pełna nazwa uwzględnia DST

Różnica między określeniem strefy skrótem a pełną nazwą może mieć istotne znaczenie. Jeśli weźmiemy pod uwagę, że skrót oznacza jedynie przesunięcie, a pełna nazwa bierze pod uwagę DST, to wtedy, na przykład, data w formacie: timestamptz ‘2021-10-31 02:00 Europe/Warsaw’ oznacza drugą w nocy (tuż po zmianie czasu) w strefie czasowej/geograficznej CET i jest to ten sam moment w czasie, co timestamptz ‘2021-10-31 02:00 CET’:

1
2
3
4
5

postgres=# select timestamptz '2021-10-31 02:00 CET' - timestamptz '2021-10-31 02:00 Europe/Warsaw';
 ?column? 
----------
 00:00:00
(1 row)

Jak wiadomo, w tym momencie (‘2021-10-31 02:00 CET’ to ‘2021-10-31 01:00 UTC’) przeszliśmy z czasu letniego (UTC+2) na czas zimowy (UTC+1) i przesunęliśmy zegarki w tył z godziny 3:00 (kiedy to byliśmy jeszcze w DST - w czasie letnim) na 2:00, co oznacza że mogliśmy rano dłużej pospać.

Ta więc godzinę i minutę wcześniej też była prawie druga w nocy - wskazanie lokalnego zegara pokazywało prawie tę samą godzinę: timestamptz ‘2021-10-31 01:59 Europe/Warsaw’, jednak w stosunku do strefy CET byliśmy przesunięci o godzinę:

1
2
3
4
5

postgres=# select timestamptz '2021-10-31 01:59 CET' - timestamptz '2021-10-31 01:59 Europe/Warsaw';
 ?column? 
----------
 01:00:00
(1 row)

O 01:59 geograficzna strefa Europe/Warsaw była wtedy jeszcze w strefie czasowej CEST (CEST = UTC+2) czyli w DST - przesunięta o dwie godziny wzgedem UTC (a jedną względem CET = UTC+1).

Patrz:

• strefy czasowe w Europie
• Rozporządzenie Prezesa Rady Ministrów z dnia 3 listopada 2016 r. w sprawie wprowadzenia i odwołania czasu letniego środkowoeuropejskiego w latach 2017–2021
• dyskusja tutaj i jeszcze tutaj

Tabele z nazwami stref czasowych

Rzućmy okiem na tabele z nazwami i skrótami stref:

• pg_timezone_names
• pg_timezone_abbrevs

Tabela z nazwami ma taką strukturę:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

postgres=# select * from pg_timezone_names limit 30;
           name            | abbrev | utc_offset | is_dst 
---------------------------+--------+------------+--------
 Greenwich                 | GMT    | 00:00:00   | f
 Turkey                    | +03    | 03:00:00   | f
 Kwajalein                 | +12    | 12:00:00   | f
 Antarctica/Syowa          | +03    | 03:00:00   | f
 Antarctica/Macquarie      | AEDT   | 11:00:00   | t
 Antarctica/McMurdo        | NZDT   | 13:00:00   | t
 Antarctica/South_Pole     | NZDT   | 13:00:00   | t
 Antarctica/Casey          | +11    | 11:00:00   | f
 Antarctica/DumontDUrville | +10    | 10:00:00   | f
 Antarctica/Troll          | +00    | 00:00:00   | f
 Antarctica/Palmer         | -03    | -03:00:00  | f
 Antarctica/Rothera        | -03    | -03:00:00  | f
 Antarctica/Vostok         | +06    | 06:00:00   | f
 Antarctica/Mawson         | +05    | 05:00:00   | f
 Antarctica/Davis          | +07    | 07:00:00   | f
 HST                       | HST    | -10:00:00  | f
 GB-Eire                   | GMT    | 00:00:00   | f
 MST7MDT                   | MDT    | -06:00:00  | t
 CET                       | CET    | 01:00:00   | f
 EST                       | EST    | -05:00:00  | f
 Canada/Eastern            | EDT    | -04:00:00  | t
 Canada/Saskatchewan       | CST    | -06:00:00  | f
 Canada/Mountain           | MDT    | -06:00:00  | t
 Canada/Central            | CDT    | -05:00:00  | t
 Canada/Yukon              | MST    | -07:00:00  | f
 Canada/Pacific            | PDT    | -07:00:00  | t
 Canada/Newfoundland       | NDT    | -02:30:00  | t
 Canada/Atlantic           | ADT    | -03:00:00  | t
 Egypt                     | EET    | 02:00:00   | f
 ROC                       | CST    | 08:00:00   | f

Natomiast tabela ze skrótami taką:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

postgres=# select * from pg_timezone_abbrevs limit 30;
 abbrev | utc_offset | is_dst 
--------+------------+--------
 ACDT   | 10:30:00   | t
 ACSST  | 10:30:00   | t
 ACST   | 09:30:00   | f
 ACT    | -05:00:00  | f
 ACWST  | 08:45:00   | f
 ADT    | -03:00:00  | t
 AEDT   | 11:00:00   | t
 AESST  | 11:00:00   | t
 AEST   | 10:00:00   | f
 AFT    | 04:30:00   | f
 AKDT   | -08:00:00  | t
 AKST   | -09:00:00  | f
 ALMST  | 07:00:00   | t
 ALMT   | 06:00:00   | f
 AMST   | 04:00:00   | f
 AMT    | -04:00:00  | f
 ANAST  | 12:00:00   | f
 ANAT   | 12:00:00   | f
 ARST   | -03:00:00  | f
 ART    | -03:00:00  | f
 AST    | -04:00:00  | f
 AWSST  | 09:00:00   | t
 AWST   | 08:00:00   | f
 AZOST  | 00:00:00   | t
 AZOT   | -01:00:00  | f
 AZST   | 04:00:00   | f
 AZT    | 04:00:00   | f
 BDST   | 02:00:00   | t
 BDT    | 06:00:00   | f
 BNT    | 08:00:00   | f
(30 rows)

Polska - strefa czasowa

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

postgres=# select * from pg_timezone_names where name ilike '%poland%'
postgres-# ;
     name     | abbrev | utc_offset | is_dst 
--------------+--------+------------+--------
 posix/Poland | CET    | 01:00:00   | f
 Poland       | CET    | 01:00:00   | f
(2 rows)

postgres=# select * from pg_timezone_names where name ilike '%warsaw%'
;
        name         | abbrev | utc_offset | is_dst 
---------------------+--------+------------+--------
 Europe/Warsaw       | CET    | 01:00:00   | f
 posix/Europe/Warsaw | CET    | 01:00:00   | f
(2 rows)

Z powyższych tabel mogę się dowiedzieć, że:

• skrót strefy czasowa dla Polski to CET (Central Europe Time)
• nazwa strefy to posix/Poland,Poland, Europe/Warsaw lub posix/Europe/Warsaw
• Polska nie jest w czasie “daylight saving time” (wartość f w kolumnie is_dst)

Ciekawe, czy gdybym sprawdziła tabelę pg_timezone_names dwa tygodnie temu, to czy zobaczyłabym inne wartości? Takie jak:

1

 Europe/Warsaw       | CEST    | 02:00:00   | t

Funkcje

Lista funkcji do pracy z typami daty/czasu jest naprawdę imponująca. Warto te funkcje przejrzeć, by mieć orientację w możliwościach postgresa.

Do tworzenia czasów

• age ( timestamp, timestamp ) → interval - różnica czasów
• current_date → date - bieżąca data
• current_timestamp → timestamp with time zone - bieżący timestamptz (zmienna)
• now ( ) → timestamp with time zone - bieżący timestamptz (funkcja)
• make_date ( year int, month int, day int ) → date - tworzenie dat z liczb
• to_timestamp ( double precision ) → timestamp with time zone - Unix timestamp do timestamptx

Do sprawdzania nakładania się interwałów

Istnieje również interesujący operator logiczny (zwracający true lub false) OVERLAPS operujący na parach: (start1, end1) OVERLAPS (start2, end2), np:

• SELECT (DATE ‘2001-02-16’, INTERVAL ‘100 days’) OVERLAPS (DATE ‘2001-10-30’, DATE ‘2002-10-30’);

Do wyciągania szczegółów

A także różne warianty EXTRACT (something FROM something), np.

• SELECT EXTRACT(QUARTER FROM TIMESTAMP ‘2001-02-16 20:38:40’); → 1
• SELECT EXTRACT(DAY FROM TIMESTAMP ‘2001-02-16 20:38:40’); → 16

Do przekształcania stref czasowych

Operator AT TIME ZONE przekształca

• czas bez strefy do określonej strefy: timestamp ‘2001-02-16 20:38:40’ at time zone ‘America/Denver’ → 2001-02-17 03:38:40+00
• a także czas ze strefą do innej strefy: time with time zone ‘05:34:17-05’ at time zone ‘UTC’ → 10:34:17+00

Podsumowanie

W tym wpisie:

• Przedstawilam krótki przegląd typów dostępnych w systemie PostgreSQL, a także omówiłam, w jaki sposób można wprowadzać wartości tych typów do systemu i jak je wypisać.
• Sprawdziłam, w jakich tabelach systemowych znajdują się skróty nazw oraz same nazwy stref czasowych.
• Nauczyłam się, że przy określeniu znacznika czasu ze strefą czasową (wartości typu timestamptz) warto wiedzieć, czy mamy do czynienia z czasem letnim czy zimpwym.
• Spojrzałam na listę funkcji związanych z czasem i przedstawiłam kilka z nich.

Mam nadzieję, że nie tylko ja nauczyłam się czegoś nowego :)

Aktualizacja:

Artykuły na temat typów danych w Postgresie: tapoueh.org: data types

Ten wpis jest częścią serii sql.

• 2021-04-11 - Postgres - daty
• 2021-16-10 - Postgres - krótka analiza danych dot. zajęć dodatkowych
• 2021-04-10 - Postgres - JSON
• 2021-04-10 - Postgres - ćwiczenia
• 2021-30-09 - Postgres - instalacja