Hoe krijg ik het huidige Unix-tijdstempel van PostgreSQL?

In postgres, timestamp with time zone kan worden afgekort als timestamptz, en timestamp without time zone als timestamp. Ik zal voor de eenvoud de kortere typenamen gebruiken.

Het Unix-tijdstempel ophalen van een postgres timestamptz zoals now() is eenvoudig, zoals u zegt, gewoon:

select extract(epoch from now()); 

Dat is echt alles wat u moet weten over het verkrijgen van de absolute tijd van alles van het type timestamptz, inclusief now().

Dingen worden alleen gecompliceerd als je een timestamp veld hebt .

Wanneer u timestamptz gegevens zoals now() in dat veld plaatst, wordt het eerst geconverteerd naar een bepaalde tijdzone (ofwel expliciet met at time zone of door te converteren naar de sessietijdzone) en de tijdzone-informatie wordt verwijderd . Het verwijst niet langer naar een absolute tijd. Dit is waarom u wilt meestal geen tijdstempels opslaan als timestamp en gebruikt normaal gesproken timestamptz – misschien wordt een film om 18.00 uur uitgebracht op een bepaalde date in elke tijdzone , dat is het soort gebruiksscenario.

Als je alleen in een enkele tijdzone werkt, kom je misschien weg met (verkeerd) gebruik van timestamp. Conversie terug naar timestamptz is slim genoeg om met zomertijd om te gaan, en voor conversiedoeleinden wordt aangenomen dat de tijdstempels in de huidige tijdzone vallen. Hier is een voorbeeld voor GMT / BST:

select "2011-03-27 00:59:00.0+00"::timestamptz::timestamp::timestamptz , "2011-03-27 01:00:00.0+00"::timestamptz::timestamp::timestamptz; /* |timestamptz |timestamptz | |:---------------------|:---------------------| |2011-03-27 00:59:00+00|2011-03-27 02:00:00+01| */ 

_DBFiddle

Maar let op het volgende verwarrende gedrag:

set timezone to 0; values(1, "1970-01-01 00:00:00+00"::timestamp::timestamptz) , (2, "1970-01-01 00:00:00+02"::timestamp::timestamptz); /* |column1|column2 | |------:|:---------------------| | 1|1970-01-01 00:00:00+00| | 2|1970-01-01 00:00:00+00| */ 

_DBFiddle

Dit is omdat :

PostgreSQL onderzoekt nooit de inhoud van een letterlijke tekenreeks voordat het type wordt bepaald, en zal daarom beide […] behandelen als tijdstempel zonder tijdzone. zorg ervoor dat een letterlijke term wordt behandeld als tijdstempel met tijdzone, geef het het juiste expliciete type … In een letterlijke die is bepaald als tijdstempel zonder tijdzone, zal PostgreSQL stilzwijgend elke tijdzone-indicatie negeren

Reacties

• Enig idee hoe je de resulterende decimaal kunt omzetten in een geheel getal zonder decimale punt (ik bedoel het samenvoegen van het getal en de decimaal als één groot geheel getal) Bedankt.
• Like dit maar ik ' weet zeker dat je ' dat niet echt wilt. Misschien wil je vermenigvuldigen met een macht van tien en alle resterende decimalen verwijderen?
• @ W.M. Misschien zo? SELECT FLOOR(EXTRACT(epoch FROM NOW())*1000);

SELECT extract(epoch from now() at time zone "utc"); 

retourneert niet het juiste tijdstempel omdat postgres tijdzoneconversie tijdzonegegevens weggooit uit het resultaat:

9.9.3. AT TIME ZONE

Syntaxis: tijdstempel zonder tijdzone AT TIME ZONE zone
Retourneert: tijdstempel met tijdzone
Behandel gegeven tijdstempel zonder tijdzone zoals gelegen in de opgegeven tijdzone

Syntaxis: tijdstempel met tijdzone AT TIME ZONE zone
Retourneert: tijdstempel zonder tijdzone
Converteer gegeven tijdstempel met tijdzone naar de nieuwe tijdzone, zonder tijdzone-aanduiding

achteraf kijkt extract naar tijdstempel zonder tijdzone en beschouwt het als een lokale tijd (hoewel het in feite al utc is).

De juiste manier zou zijn:

select now(), extract(epoch from now()), -- correct extract(epoch from now() at time zone "utc"), -- incorrect extract(epoch from now() at time zone "utc" at time zone "utc"); -- correct now | date_part | date_part | date_part -------------------------------+------------------+------------------+------------------ 2014-10-14 10:19:23.726908+02 | 1413274763.72691 | 1413267563.72691 | 1413274763.72691 (1 row) 

In de laatste regel voert de eerste at time zone de conversie uit, de tweede De ene wijst een nieuwe tijdzone toe aan het resultaat.