Hur får jag den aktuella unix-tidsstämpeln från PostgreSQL?

I postgres, timestamp with time zone kan förkortas som timestamptz och timestamp without time zone som timestamp. Jag använder de kortare typnamnen för enkelhetens skull.

Att få Unix-tidsstämpeln från en postgres timestamptz som now() är enkelt, som du säger, bara:

select extract(epoch from now()); 

Det är verkligen allt du behöver veta om att få den absoluta tiden från något av typen timestamptz, inklusive now().

Saker blir bara komplicerade när du har ett timestamp -fält .

När du lägger timestamptz data som now() i det fältet, kommer det först att konverteras till en viss tidszon (antingen uttryckligen med at time zone eller genom att konvertera till sessionens tidszon) och tidszoninformationen kasseras . Det hänvisar inte längre till en absolut tid. Det är därför du vill vanligtvis inte lagra tidsstämplar som timestamp och skulle normalt använda timestamptz – kanske en film släpps klockan 18 på en viss datum i varje tidszon , det är den typ av användningsfall.

Om du bara arbetar i en enda tidszon kan du komma undan med (mis) med timestamp. Omvandling tillbaka till timestamptz är tillräckligt smart för att klara DST, och tidsstämplarna antas för konverteringsändamål vara i den aktuella tidszonen. Här är ett exempel för 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 notera följande förvirrande beteende:

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

Detta beror på att :

PostgreSQL undersöker aldrig innehållet i en bokstavssträng innan den bestämmer dess typ och behandlar därför båda […] som tidsstämpel utan tidszon. se till att en bokstav behandlas som tidsstämpel med tidszon, ge den rätt uttrycklig typ … I en bokstav som har bestämts att vara tidsstämpel utan tidszon ignorerar PostgreSQL tyst någon tidszonindikation

Kommentarer

• Någon idé om hur man konverterar den resulterande decimalen till ett heltal utan decimal (jag menar att slå samman talet och decimaltalet som ett stort heltal). Tack.
• Gilla det här men jag ' är säker på att du inte ' verkligen vill göra det. Vill du kanske multiplicera med en kraft på tio och ta bort alla återstående decimaler?
• @ W.M. Kanske så här? SELECT FLOOR(EXTRACT(epoch FROM NOW())*1000);

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

returnerar inte rätt tidsstämpel eftersom konvertering av tidszon efter post kastar tidszonsinformation från resultatet:

9.9.3. PÅ TIDZON

Syntax: tidsstämpel utan tidszon PÅ TIDZON zon
Returnerar: tidsstämpel med tidszon
Behandla given tidsstämpel utan tidszon som lokaliserad i den angivna tidszonen

Syntax: tidsstämpel med tidszon AT TIME ZONE zone
Returnerar: tidsstämpel utan tidszon
Konvertera given tidsstämpel med tidszon till den nya tidszonen, utan tidszonbeteckning

efteråt tittar extrakt på tidsstämpel utan tidszon och anser att det är en lokal tid (även om den redan är utc).

Rätt sätt skulle vara:

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 sista raden utför den första at time zone konverteringen, seco och man tilldelar resultatet en ny tidszon.