PostgreSQL:如何从 Unix 纪元转换为日期? [英] PostgreSQL: how to convert from Unix epoch to date?

问题描述

声明给了我日期和时间.

The statement gives me the date and time.

如何修改语句以使其仅返回日期(而不是时间)?

How could I modify the statement so that it returns only the date (and not the time)?

SELECT to_timestamp( TRUNC( CAST( epoch_ms AS bigint ) / 1000 ) );

推荐答案

你使用 to_timestamp 函数，然后将时间戳转换为 date

You use to_timestamp function and then cast the timestamp to date

 select to_timestamp(epoch_column)::date;

更多细节:

/* Current time */
 select now();  -- returns timestamp

/* Epoch from current time;
   Epoch is number of seconds since 1970-01-01 00:00:00+00 */
 select extract(epoch from now()); 

/* Get back time from epoch */
 -- Option 1 - use to_timestamp function
 select to_timestamp( extract(epoch from now()));
 -- Option 2 - add seconds to 'epoch'
 select timestamp with time zone 'epoch' 
         + extract(epoch from now()) * interval '1 second';

/* Cast timestamp to date */
 -- Based on Option 1
 select to_timestamp(extract(epoch from now()))::date;
 -- Based on Option 2
 select (timestamp with time zone 'epoch' 
          + extract(epoch from now()) * interval '1 second')::date; 

在你的情况下:

 select to_timestamp(epoch_ms / 1000)::date;

PostgreSQL 文档

这篇关于PostgreSQL:如何从 Unix 纪元转换为日期?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！