如何在postgres中忽略时间部分，对具有epoch值的列执行JOIN查询

Question

我有两个表，比如表a和表B，这两个表都有一个列creation_date，其中有epoch值。我想在creation_date上加入这两个表格，忽略其中的时间值。

假设epoch值是1603385466134，则转换为Thu Oct 22 2020 22:21:06。现在加入应该作为Thu Oct 22 2020 00:00:00发生

我试过这个，但没有工作

Select t.lr_transaction_id, t.unique_customer_id, t.transaction_id 
from boidcrewardz.transaction_temp t 
join boidcrewardz.transaction_dump d 
on  t.first_6_digit_card = d.first_6_digit_card 
and  t.transaction_amount = d.transaction_amount 
and date_trunc('day',t.transaction_date) = date_trunc('day',d.transaction_date)  
order by t.creation_date desc

Answer 1

Postgres 9及更高版本支持to_timestamp():

to_timestamp(双精度(→带时区的时间戳

将Unix epoch(自1970-01-01 00:00:00+00以来的秒数(转换为时间戳带时区

to_timestamp(1284352323(→2010-09-13 04:32:03+00

Postgres 8支持SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 1603385466.134 * INTERVAL '1 second'

您只需要将epoch值除以1000.00，因为您的epoch是以毫秒为单位计算的，而Postgres预计为秒。

以下其中一项应该有效：

select date_trunc('day',to_timestamp(1603385466134/1000.00));
SELECT date_trunc('day',TIMESTAMP WITH TIME ZONE 'epoch' + 1603385466134/1000.00 * INTERVAL '1 second');