sql - 将 sqlite 转换为 postgres 查询

Question

谁能帮我把这个 sqlite 查询转换成 postgres 查询？

SELECT count(*), count(*), date(date, '-'||strftime('%w',date)||' days') as date
FROM emails as m, contacts as me
WHERE datetime(date) > datetime('2010-08-25')
  and datetime(date) < datetime('2011-08-25')
  and (me.id = m.fr)
  and me.email like '%gmail.com%'
GROUP BY date
ORDER BY date asc

更新，我找到了答案:

select count(*), (m.date::date - extract(dow from m.date)::int) as dat
from emails as m join contacts as me on m.fr = me.id
where m.date > '2010-08-25'
  and m.date < '2011-08-25'
  and me.email like '%gmail.com%'
group by dat
order by dat

Answer 1

strftime 业务已处理 elsewhere所以我们只需要在这里整理出 datetime(...) 的东西并补偿 date 作为时间戳。当我在这里时，我将切换到显式连接条件(而不是 WHERE 子句中的隐式连接条件)。

select count(*), count(*), m.date::date - extract(dow from m.date)::int as date
from emails as m join contacts as me on m.fr = me.id
where m.date > '2010-08-25'
  and m.date < '2011-08-25'
  and me.email like '%gmail.com%'
group by m.date
order by m.date asc

PostgreSQL 可以自行将时间戳与 ISO8601 日期字符串进行比较，因此您不需要为比较进行任何转换或重新格式化。

这两个 count(*) 对我来说仍然有点滑稽，但我不知道使用查询的上下文，所以重复可能有意义。