SQLite：以两天给定日期之间的日，小时，分钟表示差异 [英] SQLite: express the difference as days, hours, minutes between two given dates

问题描述

作为SQLite查询输出，我试图以天，小时和分钟（如1天，6小时，17分钟）表示两个给定日期的差异。我在SQLitedatabase中有 entryin 和 entryout as datetime 。我尝试了 julianday 和 strftime 的所有组合，但仍然会恶化天气。

I am trying to express the difference of two given dates in days, hours, and minutes (like 1 day, 6 hours, 17 minutes.) as SQLite query output. I have entryin and entryout as datetime fields in a SQLitedatabase. I tried all combinations of julianday and strftime but still running into rough weather.

我试过 strftime（'％d％H：％M'，julianday（entryout）-julianday（entryin））。对于一行，值为 2011-11-10 11:46 和 2011-11-09 09:00 。但输出是 25 14:46 而不是 01 02:46 。

I tried strftime('%d %H:%M', julianday(entryout)-julianday(entryin)). For a row the values are 2011-11-10 11:46, and 2011-11-09 09:00. but the output is 25 14:46 instead of 01 02:46.

有人可以帮助我吗，还是指出我正确的逻辑？谢谢提前。

Can some one help me with this, or point me correct logic for this? Thanks in advance.

推荐答案

你可以尝试这样的一个：

You can try something like this:

SELECT
    CAST((strftime('%s', '2011-11-10 11:46') - strftime('%s', '2011-11-09 09:00')) / (60 * 60 * 24) AS TEXT) || ' ' ||
    CAST(((strftime('%s', '2011-11-10 11:46') - strftime('%s', '2011-11-09 09:00')) % (60 * 60 * 24)) / (60 * 60) AS TEXT) || ':' ||
    CAST((((strftime('%s', '2011-11-10 11:46') - strftime('%s', '2011-11-09 09:00')) % (60 * 60 * 24)) % (60 * 60)) / 60 AS TEXT);

这篇关于SQLite：以两天给定日期之间的日，小时，分钟表示差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！