如何获取两个日期戳之间的时差并按降序排列

Question

我需要两个日期戳之间的时差(以小时为单位(，需要按降序排列它们以获得中位数

SELECT "fulfillmentshistories"."fulfillmentid" as "Fulfillment ID" FROM FULFILLMENTHISTORIES
WHERE (Fulfillments."status" IN ('paid','delivered') AND (DATE("fulfillmenthistories"."createdAt") BETWEEN DATE('{{startDate}}') AND DATE('{{endDate}}')))
union
SELECT DATEDIFF(HOUR,   'startDate'-'endDate') as DATEDIFF;
ORDER BY DATEDIFF, DESC

这是我尝试过的，请帮助

SELECT "fulfillmentshistories"."fulfillmentid" as "Fulfillment ID" FROM    FULFILLMENTHISTORIES
WHERE (Fulfillments."status" IN ('paid','delivered') AND (DATE ("fulfillmenthistories"."createdAt") BETWEEN DATE('{{startDate}}') AND DATE('{{endDate}}')))
union
SELECT DATEDIFF(HOUR,   'startDate'-'endDate') as DATEDIFF;
ORDER BY DATEDIFF, DESC

运行查询时出错：在"ORDER"处或附近出现语法错误 第 5 行：按日期排序差异，DESC ^

Answer 1

SELECT DATEDIFF(HOUR, 'startDate'-'endDate') as DATEDIFF; ORDER BY DATEDIFF, DESC

应该是

SELECT DATEDIFF(HOUR, 'startDate'-'endDate') as DATEDIFF ORDER BY DATEDIFF, DESC ;

并尝试避免使用与函数名称相似的别名。

Answer 2

UNION
SELECT DATEDIFF(HOUR,   'startDate'-'endDate') as DATEDIFF;
ORDER BY DATEDIFF, DESC

应该是

UNION
SELECT DATEDIFF(HOUR,   'startDate'-'endDate') as DATEDIFF
ORDER BY DATEDIFF DESC;

删除 DATEDIFF

后面的分号和 ORDER BY DATEDIFF 之后的逗号。分号应该在 SQL 语句的末尾，因为整个都是一个 sql 语句。