Konwertowanie dat do tej samej strefy czasowej na postgresql

0

Witam,

Pracuje obecnie na projekcie, gdzie wszystkie daty sa konwertowane do wybranego przez uzytkownika timezone. Czyli np uzytkownik sie rejestruje i wybiera sobie swoj TZ. Baza danych zapisuje wszsytie daty w Europe/Warsow TZ. Problem jest tego typu, ze jak tworze raport CSV to mam firmy z kilkoma roznymi strefami czasowymi i nie moge zrobic porownan na datach, bo daty maja inne TZ. Czyli trzeba przeprowadzic jakas konwersje na strefach czasowych.

Czyli tak firmy oraz zamowienia moga miec totalnie rozne strefy czasowe. Czyli najlepiej konwertowac zamowienia do TZ firmy. Jakis pomysl jak pokonwertowac te daty?

SELECT * 
FROM companies
JOIN order ON order.id = companies.order_id
WHERE order.created > '2020-07-01 00:00:00'

Dajmy na to, ze firma jest w Europe/Warsow ale zamowienia nie wiem europe/lisbon, europe/london etc. Wiec musimy, kazde zamowienie przekonwertowac z lisbony oraz london do warsow. I do tego wszystkiego nasza postgresowa baza danych jest na UTC :]

0

Ja nie widzę problemu... Gdzie masz zapisane te daty? W CSV, czy w bazie? Jeśli w bazie, to jaki jest typ pola?

0

@Marcin.Miga Wszystko jest w bazie danych. Tj. company(created, time_zone) i order(ordered, time_zone). Zastanawialem się, ze moze warto stworzyć jedna perspektywe i w niej przechowywac wszystkie przekonwertowane strefy czasowe. Zamiast za kazdym razem jak musze wygenerowac raport to konwertowac to w locie. Ewentualnie moge dodac 1 albo 2 kolumny do obu tabel, ktore beda przechowywac przekonwertowana date do Europe/London. Moge uzyc trigger'ow zeby zapisywac konwersje.

I obawiam sie o wydajność bazy danych prz konwersji w locie.

0

Ale jakieś straszne herezje piszesz...
Zaraz z kompa dam przykład...

4
drop table if exists test_tz;

create table test_tz
(
id serial not null primary key,
czas timestamp WITH time zone
)
;

set time zone default;

insert into test_tz(czas) values ('2020-07-28 23:15:00'); -- czas default, czyli Warszawa

set time zone 'Europe/Lisbon'; -- +1 czyli Portugalia

insert into test_tz(czas) values ('2020-07-28 22:15:00'); -- czas portugalski

set time zone 0; -- GMT

insert into test_tz(czas) values ('2020-07-28 21:15:00'); -- czas GMT

select * from test_tz

Jak widzisz wstawiam sobie do tabeli 3 rekordy - każdy wg innego czasu, ale aktualny cxzas dla tej strefy czasowej.
Gdy zrobie SELECT, to dostanę 3 rekordy z takimi samymi wartościami.
Co więcej - z wartościami dla aktualnie ustawionej TZ. Czyli tu w wyniku opwinienes dostać 3 razy 2100+00.
A wystarczy, że zmienię na:

set time zone 'Europe/Warsaw'

to w wynikach powinna być 23:15.
Tak więcdo bazy idzie ujednolicony czas. Oczywiście pod warunkeim, że masz pole z TZ.

0

Ah, rozumiem o co Ci chodzi, faktycznie dosc prosto to zrobiles. Tylko, ze ja mam troche innego typu problem. Nie moge ustawic strefy na cala baze tak jak Ty to zrobiles. Mam na stronie mozliwosc stworzenia raportu wygenerowanego w CSV. Raport o firmach i zamowieniach w nich zlozonych. Do tego mam tylko jeden filtr z data od/do. Jest to data rejestracji firmy w systemie. Data w tym filtrze nie ma strefy czasowe i nie moze miec. Takze mi sie wydaje, ze musze ta date z filtra przekonwertowac na strefe firmy i wtedy porownac.

Masz np data utworzenia firmy od 2020-01-01 do 2020-06-30.

Firma 1 Warszawa 2020-01-01 0000
Firma 2 Londyn 2020-06-30 2359
Firma 3 Japonia 2020-06-30 2359

Nie wiem czy dobrze to tlumacze, ale ja to tak widze.

2
drop table if exists test_tz;

create table test_tz
(
id serial not null primary key,
czas timestamp WITHOUT time zone,
tz varchar(100)
)
;

insert into test_tz(czas, tz) 
values
	('2020-07-29 10:30', 'Europe/Warsaw'),
	('20200729T1030', 'Europe/Lisbon'),
	('2020-07-29 10:30', '-1')
;	

select *, timezone(tz, czas) from test_tz

EDIT:

select *, timezone(tz, czas), timezone('Europe/London', timezone(tz, czas)) from test_tz
0

edit: Przepraszam, nie zauwazylem, ze dodales edit. Wlasnie to bedzie podwojna konwersja. To bedzie dzialac, ale czy na pewno nie zabije to bazy danych? Tabele tj firma czy zamowienia maja tysiace rekordow. One sa just dosc spore.

0

Liczba rekordów niewiele mówi na temat wielkości bazy, jeśli nie wiadomo, jaka jest struktura bazy, ani jak te dane są wyciągane. Niemniej jednak "tysiące" to raczej nie jest rząd wielkości, który powoduje czkawkę w bazie, nawet na średnim serwerze. Wszystko jednak zależy, co z tymi danymi robisz i jak często to robisz.
Jeśli potrzebujesz co kilka minut pobrać dane kilkudziesięciu zamówień wraz z przekonwertowanymi datami, to raczej tego nie odczujesz. Jeśli jednak zamierzasz nieustannie pobierać listę wszystkich (powiedzmy, że miliona) zamówień i dla każdego przeliczać te daty, lub choćby wyszukiwać zamówienia po przekonwertowanej dacie, to... nie tędy droga.

0

OK, perspektywa nie przyspieszy konwertownia stref czasowych. To co myslici o tym, zeby dodac jedna nowa kolumne do tabel z juz przekonwertowana godzina? I triggery, czyli za kazdym razem jak bedzie dodawana albo aktulizowana firma to odpalam trigger i on doda przekonwertowana wartosc. Ew moge stworzyc nowa tabele i trzymac wszsytkie daty w jednej tabeli. Ma to wiekszy sens?

0

**(1) **MATERIALIZED VIEW ma dosc sens. Dziala podobnie do perspektywy, ale jednak przechowuje dane na dysku. Prawie jak cache w bazie danych. Dobrze wiedziec. Tylko, nie wiem czy za bardzo nie kombinuje moze nie ma sensu tego optymalizowac w ogole i zostawiec tak jak podal Marcin:

select *, timezone(tz, czas), timezone('Europe/London', timezone(tz, czas)) from test_tz

Jak stworze materialized view to tez JOIN zwolni delikatnie zapytanie. Do tego mam kilka tabel do przechowywania w tej zmaterializowanej perspektywie. Wiec musze miec kolumy tj jak type, type_id, converted_date. Gdzie type to np ENUM('company', 'orders', 'items') etc.

edit Tylko czytam, ze spory problem jest z odswiezaniem materialized view. To jest bardziej przydatne jak jest np raport generowany raz na dzien. To w nocy mozna uruchodzic cron, ktory odswiezy materialised view. Ponoc to nie takie oczywiste. Wiec moze odpadac jednak. Wydaje sie, ze dodatkowa np tabela i triggery szybciej zalatwia sprawe.
**
(2) **Generated Columns Wydaje sie, ze to jest dosc nowe. Wprowadzone dopiero w wersji 12. Ja uzywam 9.6. Ale tez dobrze wiedziec. Ah, to dodales w swoim komentarzu.

2

Materialized View ma tę zaletę, że może mieć indeksy (w odróżnieniu od zwykłego View).
Musiałbyś sprawdzić, jak zadziała ci odświeżanie view każdorazowo po wrzuceniu danych . Trigger AFTER STATEMENT.

0

@Marcin.Miga Sprawdze, juz mam stworzona Materialized View, bo to ciekawe, ale czy się to w ogole oplaca? I tak musze robic JOIN do głównego zapytania. Z tego co ja rozumiem jak działa MV to chyba troche jednak inne jest jej przeznaczenie. Może lepiej dodać nowa tabele(type, type_id, date). I trzymac daty ze wszsytkich tabel w tej jednej tabeli.

Druga sprawa, ze te MV bedzie strasznie bombardowane nowymi krotkami. I teraz dodać odswieżanie po każdej dodaniu albo aktualizacji. To troche dużo operacji. Poza tym, chyba jak sie odswieza MV to całość, wszystkie krotki, a nie pojedyncze. Nie widze w postgresql opcji tj Always fresh.

3

TO zrób sobie taki MV, byś nie musiał już JOINować. Tak troszkę zdenormalizuj sobie dane :) A odświeżanie działa w miarę szybko. Tylko nie możesz tego robić po każdym rekordzie, bo to bez sensu. Możesz to robić albo co jakiś czas (JOB/Cron), albo Trigerrem AFTER STATEMENT, albo dopiero wtedy, gdy ci dane będą potrzebne.

0

Ale jak mam odswiezyc? REFRESH MATERIALIZED VIEW CONCURRENTLY myview odswieza cala MV do tego blokuje dostep do MV. Z ta opcja prosty update pojedynczej krotki trwa ponad 5 sek.

0

W MV zalozenie jest takie, ze odswiezasz wszystko albo nic. Dodatkowo, odswiezanie blokuje MV.

1
poniatowski napisał(a):

W MV zalozenie jest takie, ze odswiezasz wszystko albo nic. Dodatkowo, odswiezanie blokuje MV.

Tak się zastanawiam - skoro postanowiłeś trzymać datę wpisu przekonwertowaną do jednej strefy czasowej (przyjmuję, że UTC), to może zamiast cudować z widokami dodaj do tabeli nową kolumnę, załóż na nią indeks, a triggerem po dodaniu rekordu przeliczaj jej wartość... Przy wpisie będziesz przeliczał tylko nowe rekordy, przy updacie tylko zmienione, zawsze będziesz miał aktualne dane i nie będziesz musiał joinować przy zapytaniu.

Jako wisienka na torcie - jeśli będziesz chciał pobrać wpisy starsze od 2020-07-30 2100 w strefie Warszawskiej (+02.00), to w WHERE przekonwertuj tę jedną datę na UTC i porównuj z wartościami z kolumny UTC. Wtedy dla 20k rekordów przy odczycie będziesz miał nie 40k, nie 20k, lecz tylko jedną konwersję.

ALE! Ja Postgresa nie używałem nigdy - tak sobie tylko myślę :)

0

Zgadzam sie @Fac z Toba. Tylko daty, ktore musze konwertowac mam conajmniej w kilku tabelach. A te tabele maja juz teraz po 130, 80, 50 kolumn. I tak myslalem, ze moze lepiej trzymac wszystkie daty w jednym miejscu. JOIN z reguly jest szybki. Stworzyl bym 2 dodatkowe tabele. Jedna z nazwami tabel, a druga just tz_conversion(table_name_id, date).

3

Jeżeli zakładasz, że karmisz dane czasem z róznych stref, to IMO w bazie trzymasz ustandaryzwoany czas np. UTC, przy wyszukiwaniu sprawdzasz z jekiej strefy czasowej następuje zapytanie i konwertujesz parametry, a nie dane....

https://stackoverflow.com/questions/18188151/how-to-convert-local-time-to-utc

0

@Panczo: Fakt, polac Ci trzeba dobrego trunku, bo dobre komentarze piszesz. Fakt, przetestowalem dziala!! W wypadku przeszukiwania danych po datcie wejsciowej. Musze sprawdzic inne przypadki, bo mozliwe, ze i tak bede potrzebowal takiej konwersji np do raportow, gdzie musze pokazac jedna TZ.

1

Konwersja do pokazywania to normalna sprawa i jej nie unikniesz.
Tu chodzi o łatwość szukania/wybierania, a to jest zdecydowanie łatwiejsze jak dane są ustandaryzowane.

0

Generalnie postgres jest ustawiony na UTC (GMT-0), a wszystkie dane przechowywane w europe/warsaw. Wiec tak jak napisales. Konwertuje jedynie dane wejsciowe/filtry daty z formularza na strefe czasowa firmy a nastepnie na europe/warsaw. I dziala :)

1 użytkowników online, w tym zalogowanych: 0, gości: 1