时间戳

首页 > 解决方案 > SQL查询日期，奇怪的格式

sql - SQL查询日期，奇怪的格式

问题描述

我正在尝试分析 MYSQL 表。

我在列中有日期时间，表示项目的开始和结束时间。

{"stmt":"SELECT start_at, end_at FROM trafficdata;","header":
["start_at","end_at"],

"rows":

[["1022290663000","1022809063000"],["1172792113000","1173483313000"],["1351803408000","1353531408000"],["1290517173000","1293022773000"]

希望使用 SQLite 以小时格式将其输出到日期时间

标签： sqlsqlite

解决方案

这些值可能是一个整数，计数自 1970 年 UTC 第一时刻的纪元参考以来的毫秒数，即 1970-01-01T00:00Z。

Instant这是一些将这些数字解析为对象的 Java 代码。

List< Long > inputs = List.of( 1_022_290_663_000L , 1022809063000L , 1172792113000L , 1173483313000L , 1351803408000L , 1353531408000L , 1290517173000L , 1293022773000L ) ;
for( Long input : inputs ) 
{
    Instant instant = Instant.ofEpochMilli( input ) ;
    System.out.println( input + " → " + instant ) ;
}

请参阅在 IdeOne.com 上实时运行的代码。

1022290663000 → 2002-05-25T01:37:43Z

1022809063000 → 2002-05-31T01:37:43Z

1172792113000 → 2007-03-01T23:35:13Z

1173483313000 → 2007-03-09T23:35:13Z

1351803408000 → 2012-11-01T20:56:48Z

1353531408000 → 2012-11-21T20:56:48Z

1290517173000 → 2010-11-23T12:59:33Z

1293022773000 → 2010-12-22T12:59:33Z

To calculate the elapsed time between a pair, subtract the earlier number from the later number, divide by 1,000 for a count of seconds, divide by 60 for a count of minutes, and divide by 60 again for a count of hours.

I do not use SQLite, but I imagine you can do this using arithmetic operators found in SQLite.

Perhaps something like:

SELECT ( ( end_at - start_at ) / 1000 / 60 / 60 ) 
FROM whatever ;

推荐阅读