Hvordan får jeg det aktuelle unix-tidsstempel fra PostgreSQL?

I postgres, timestamp with time zone kan forkortes som timestamptz og timestamp without time zone som timestamp. Jeg vil bruge de kortere typenavne for at gøre det nemmere.

At få Unix-tidsstemplet fra en postgres timestamptz som now() er simpelt, som du siger, bare:

select extract(epoch from now()); 

Det er virkelig alt hvad du behøver at vide om at få den absolutte tid fra noget af typen timestamptz, inklusive now().

Ting bliver kun komplicerede, når du har et timestamp felt .

Når du lægger timestamptz data som now() i det felt, konverteres de først til en bestemt tidszone (enten eksplicit med at time zone eller ved at konvertere til sessionens tidszone), og tidszoneoplysningerne kasseres . Det refererer ikke længere til en absolut tid. Dette er grunden til du vil normalt ikke gemme tidsstempler som timestamp og normalt bruge timestamptz – måske frigives en film kl. 18 på en bestemt dato i hver tidszone , det er den slags brugssag.

Hvis du kun nogensinde arbejder i en enkelt tidszone, kan du muligvis slippe af med (mis) ved hjælp af timestamp. Konvertering tilbage til timestamptz er klog nok til at klare DST, og tidsstemplerne antages af konverteringsformål at være i den aktuelle tidszone. Her er et eksempel på 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

Men bemærk følgende forvirrende opførsel:

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

Denne er fordi :

PostgreSQL undersøger aldrig indholdet af en bogstavelig streng, inden den bestemmer dens type, og vil derfor behandle begge […] som tidsstempel uden tidszone. sørg for, at en bogstavelig behandles som tidsstempel med tidszone, giv den den korrekte eksplicitte type … I en bogstav, der er bestemt til at være tidsstempel uden tidszone, ignorerer PostgreSQL lydløst enhver tidszoneindikation

Kommentarer

• Enhver idé om, hvordan man konverterer den resulterende decimal til et heltal uden decimaltegn (jeg mener at flette tallet og decimalet som et stort heltal). Tak.
• Ligesom dette men jeg ' er sikker på at du ikke ' ikke virkelig vil gøre det. Måske vil du multiplicere med en styrke på ti og fjerne eventuelle resterende decimaler?
• @ W.M. Måske sådan her? SELECT FLOOR(EXTRACT(epoch FROM NOW())*1000);

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

returnerer ikke det korrekte tidsstempel, fordi konvertering af tidszone efter post kaster tidzoneinformation fra resultatet:

9.9.3. PÅ TIDZONE

Syntaks: tidsstempel uden tidszone PÅ TIDSZONE zone
Returnerer: tidsstempel med tidszone
Behandl givet tidsstempel uden tidszone som placeret i den angivne tidszone

Syntaks: tidsstempel med tidszone AT TIME ZONE zone
Returnerer: tidsstempel uden tidszone
Konverter givet tidsstempel med tidszone til den nye tidszone uden tidszone betegnelse

bagefter ser ekstrakt på tidsstempel uden tidszone og betragter det som en lokal tid (selvom det allerede er utc faktisk).

Den korrekte måde ville være:

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) 

I den sidste linje udfører den første at time zone konverteringen, seco og man tildeler resultatet en ny tidszone.