sqlite - sqlite中与strftime的日期比较

Question

我正在比较一个以微秒为单位的 unix 时间戳和一个带有 strftime 的时间。为此，我需要使用一个通用单元。我不明白为什么它在微秒内工作(即通过将 strftime 乘以 100 万)，但反之则不然。

SELECT

strftime("%s", "2017-04-01 00:00:00") AS Apr1st,
1492605353892000/1000000 as d,

strftime("%s", "2017-04-01 00:00:00")*1000000 As Apr1st_us,
1492605353892000 AS d_us,

1492605353892000/1000000 > strftime("%s", "2017-04-01 00:00:00") AS cmp,
1492605353892000/1000000 - strftime("%s", "2017-04-01 00:00:00") AS diff,

1492605353892000 > strftime("%s", "2017-04-01 00:00:00")*1000000 AS cmp_us,
1492605353892000 - strftime("%s", "2017-04-01 00:00:00")*1000000 AS diff_us

;

这给了我:

|     Apr1st |          d |        Apr1st_us |             d_us | cmp |    diff | cmp_us |       diff_us |
|------------|------------|------------------|------------------|-----|---------|--------|---------------|
| 1491004800 | 1492605353 | 1491004800000000 | 1492605353892000 |   0 | 1600553 |      1 | 1600553892000 |

Answer 1

strftime返回一个字符串，因此当与整数比较时，the integer is always less .带有 strftime(...)*1000000 的版本之所以有效，是因为乘法将其转换为整数，从而允许对数字进行比较。

类型转换 strftime返回值使它正确:

1492605353892000/1000000 > CAST(strftime("%s", "2017-04-01 00:00:00") AS INT)