postgre删除 SQL 中created_at的毫秒部分以与其他日期时间进行比较

Question

我想在我的日期时间找到重复项。由于毫秒的原因，我的created_at与任何行都不相等，因此我想将其删除，以便我现在可以使用相同的created_at显示所有重复项。但是此语法返回一个错误，该错误datetime column does not exist in my CONVERT

SELECT
sd.global_code AS sd_id,
sm.tag AS tag,
SUM(sm.quantity) AS quantity,
CONVERT(datetime, CONVERT(varchar, sm.created_at, 120), 120) AS date ## returns an error ##
FROM stock_distributions sd
INNER JOIN stock_movements sm ON sd.id = sm.stock_distribution_id
WHERE (sm.tag = 'Shelf Transfer' OR sm.tag = 'Transfer (Put-Away to Shelf)')
AND sl.site_id = 4
GROUP BY sd.global_code
HAVING SUM(sm.quantity) < 0
AND COUNT(convert(datetime, convert(varchar, sm.created_at, 120), 120)) > 1

我做的对吗？有没有一种更快的方法来选择具有相同日期和时间的sm.created_at，直到只有秒，这样输出就会像2019-03-31 4:08:33一样？

Answer 1

您可以通过将日期转换为字符串来使用字符串比较

to_char( mydate, 'rrrrmmddhh24ss' ) 

Answer 2

你的代码看起来像SQL Server。 它需要大量的修复。 例如，SELECT与GROUP BY键不一致。

如果您想同时查找全局代码，但忽略毫秒，我建议您datetimefromparts()：

SELECT sd.global_code AS sd_id,
datetimefromparts(year(sm.created_at), month(sm.created_at), day(sm.created_at),
datepart(hour, sm.created_at), datepart(minute, sm.created_at), datepart(second, sm.created_at),
0
) as dt
SUM(sm.quantity) AS quantity
FROM stock_distributions sd INNER JOIN
stock_movements sm
ON sd.id = sm.stock_distribution_id
WHERE sm.tag IN ('Shelf Transfer', sm.tag = 'Transfer (Put-Away to Shelf)') AND
sl.site_id = 4
GROUP BY sd.global_code, 
datetimefromparts(year(sm.created_at), month(sm.created_at), day(sm.created_at),
datepart(hour, sm.created_at), datepart(minute, sm.created_at), datepart(second, sm.created_at),
0
)
HAVING SUM(sm.quantity) < 0 AND COUNT(*) > 1;

在Postgres中，这要简单得多：

SELECT sd.global_code AS sd_id,
date_trunc('second', sm.created_at) as dt,            SUM(sm.quantity) AS quantity
FROM stock_distributions sd INNER JOIN
stock_movements sm
ON sd.id = sm.stock_distribution_id
WHERE sm.tag IN ('Shelf Transfer', sm.tag = 'Transfer (Put-Away to Shelf)') AND
sl.site_id = 4
GROUP BY sd.global_code, dt
HAVING SUM(sm.quantity) < 0 AND COUNT(*) > 1;