Postgres选择从昨天起存储为纪元的记录

时间:2022-10-01 09:27:31

I need to select all records from yesterday. Date in table is stored as epoch timestamp. I tried something like this, but it's not working.

我需要从昨天选择所有记录。表中的日期存储为纪元时间戳。我尝试过类似的东西,但它没有用。

select to_char((SELECT TIMESTAMP WITH TIME ZONE 'epoch' + history.clock * INTERVAL '1 second') , 'YYYY-MM-DD HH24:MI:SS ') AS Data
from history
WHERE
history.clock >=  EXTRACT( epoch FROM current_timestamp - interval '1 day') and
history.clock <=  EXTRACT( epoch FROM current_timestamp - interval '1 day') ;

1 个解决方案

#1


0  

Well your problem is that you're saying "everything where clock >= 1510444800 and clock <= 1510444800". You're giving yourself a single second range.

那么你的问题是你说“时钟> = 1510444800和时钟<= 1510444800”。你给自己一个单一的范围。

The following query should do what you need. Increase the upper bound of the range by 86399 (There are 86400 seconds in a day, but we already have the first second of the day from our extract when combined with a ::date cast)

以下查询应该执行您需要的操作。增加范围的上限86399(一天有86400秒,但是我们已经从我们的提取中获得了与:: date cast相结合的当天的第一秒)

select
    to_char(to_timestamp(history.clock), 'YYYY-MM-DD HH24:MI:SS ') as Data
from
    history
where
    history.clock between extract(epoch from 'yesterday'::date)::integer and (extract(epoch from 'yesterday'::date)::integer + 86399)

I've also included a few what I consider to be improvements. No need to mess around with intervals for getting yesterday when Postgres has support for casting 'yesterday' to a date. Comparing a value to an upper and lower bound is what the between keyword was made for, and there's a built in function for converting from a unix timestamp.

我还提到了一些我认为是改进的内容。当Postgres支持将“昨天”投射到约会时,不需要为昨天获得间隔而烦恼。将值与上限和下限进行比较是为关键字创建的,并且有一个用于从unix时间戳转换的内置函数。

#1


0  

Well your problem is that you're saying "everything where clock >= 1510444800 and clock <= 1510444800". You're giving yourself a single second range.

那么你的问题是你说“时钟> = 1510444800和时钟<= 1510444800”。你给自己一个单一的范围。

The following query should do what you need. Increase the upper bound of the range by 86399 (There are 86400 seconds in a day, but we already have the first second of the day from our extract when combined with a ::date cast)

以下查询应该执行您需要的操作。增加范围的上限86399(一天有86400秒,但是我们已经从我们的提取中获得了与:: date cast相结合的当天的第一秒)

select
    to_char(to_timestamp(history.clock), 'YYYY-MM-DD HH24:MI:SS ') as Data
from
    history
where
    history.clock between extract(epoch from 'yesterday'::date)::integer and (extract(epoch from 'yesterday'::date)::integer + 86399)

I've also included a few what I consider to be improvements. No need to mess around with intervals for getting yesterday when Postgres has support for casting 'yesterday' to a date. Comparing a value to an upper and lower bound is what the between keyword was made for, and there's a built in function for converting from a unix timestamp.

我还提到了一些我认为是改进的内容。当Postgres支持将“昨天”投射到约会时,不需要为昨天获得间隔而烦恼。将值与上限和下限进行比较是为关键字创建的,并且有一个用于从unix时间戳转换的内置函数。