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时间戳转换的内置函数。