sqlite - 将sqlite转换为postgres

我还有一个sqlite脚本需要转换成postgres。如果你能帮忙的话，带上一只雄鹅：）

SELECT count(*), strftime('%H', sentdate) as hour FROM latency l, contacts me
   WHERE l.lat < 1 and datetime(sentdate) > datetime('2009-01-01') and datetime(sentdate)
       < datetime('2011-02-01') and (me.id = l.replyuid or me.id = l.senduid)
   GROUP BY hour ORDER BY hour asc;

最佳答案

假设sentdate是一个日期/时间戳字段，那么它应该可以工作：

SELECT COUNT(*),
    date_part('hour', sentdate ) AS hour
FROM latency l,
    contacts me
WHERE l.lat < 1
    AND date_trunc ('day', sentdate) > DATE ( '2009-01-01' )
    AND date_trunc ('day', sentdate) < DATE ( '2011-02-01' )
    AND ( me.id = l.replyuid
        OR me.id = l.senduid )
GROUP BY date_part('hour', sentdate )
ORDER BY hour ASC;