考慮到下面的示例表，我想知道我們如何計算連續日期時間的差異（例如以天或秒為單位）：
time value
2020-03-30 00:25:10 10112
2020-04-02 08:04:03 45665
2020-04-10 09:55:56 112
2020-04-10 13:12:00 858
2020-04-28 10:15:59 89965
2020-05-30 22:31:02 12
這是由熊貓' diff():
0 NaT
1 3 days 07:38:53
2 8 days 01:51:53
3 0 days 03:16:04
4 17 days 21:03:59
5 32 days 12:15:03
換句話說，什么是 Postgres 相當于 pandas' diff()？
uj5u.com熱心網友回復：
您可以簡單地減去一個滯后的時間戳，然后以間隔或您希望使用的格式輸出to_char。例如：
SELECT "time" - LAG("time") OVER (ORDER BY "time") AS diff,
TO_CHAR("time" - LAG("time") OVER (ORDER BY "time"), 'DD "days" HH24:MI:SS') AS cdiff
FROM data
輸出：
diff cdiff
null null
{"days":3,"hours":7,"minutes":38,"seconds":53} 03 days 07:38:53
{"days":8,"hours":1,"minutes":51,"seconds":53} 08 days 01:51:53
{"hours":3,"minutes":16,"seconds":4} 00 days 03:16:04
{"days":17,"hours":21,"minutes":3,"seconds":59} 17 days 21:03:59
{"days":32,"hours":12,"minutes":15,"seconds":3} 32 days 12:15:03
db-fiddle 上的演示
請注意，如果您還想要一個行號，您可以添加一個作為
ROW_NUMBER() OVER (ORDER BY "time") - 1 AS rownum
您還可以使用以下方法以秒為單位獲取值EXTRACT：
EXTRACT(EPOCH FROM "time" - LAG("time") OVER (ORDER BY "time")) AS seconds
輸出
rownum diff cdiff seconds
0 null null null
1 {"days":3,"hours":7,"minutes":38,"seconds":53} 03 days 07:38:53 286733
2 {"days":8,"hours":1,"minutes":51,"seconds":53} 08 days 01:51:53 697913
3 {"hours":3,"minutes":16,"seconds":4} 00 days 03:16:04 11764
4 {"days":17,"hours":21,"minutes":3,"seconds":59} 17 days 21:03:59 1544639
5 {"days":32,"hours":12,"minutes":15,"seconds":3} 32 days 12:15:03 2808903
db-fiddle 上的演示
uj5u.com熱心網友回復：
SELECT row_number() OVER w - 1,
time - lag(time) OVER w
FROM tab
WINDOW w AS (ORDER BY time)
ORDER BY time;
row_number計算結果行，并按lag定義的順序從前一行獲取值。
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/451177.html
標籤：Python 数据库 PostgreSQL 约会时间
下一篇：Postgresql(psycopg2.errors.DuplicateTable)關系“idx_xxx_geometry”已經存在