我有一个带有TEXT列的SQLite数据库,该列存储DateTime值的格式如下:“ 11/08/2019 00:00:00”。我想将整个列的内容转换为UTC Epoch时间戳本地时区。 是否可以使用支持的SQL语法运行带有DateTime函数的Update SQL字符串来执行此任务,还是应该编写一个快速的C#控制台应用程序来执行此任务? 在这种情况下,我没有在网上或在SO中找到可以满足我需求的示例。
ztwsiping 回答:在sqlite数据库中更新或转换日期时间值 预转换:-转化后预转换(混合数据):-转换后:-
UPDATE SQL,例如:-
UPDATE mytable SET mycolumn =
CASE WHEN substr(mycolumn,3,1) = '/'
THEN
strftime('%s',substr(mycolumn,7,4)||'-'||substr(mycolumn,4,2)||'-'||substr(mycolumn,1,2)||' '||substr(mycolumn,12,8))
ELSE
mycolumn
END
;
可以使用。
示例
也许考虑以下将转换列的方法(或者如果已经转换(或者如果它与dd / mm / yyyy格式不匹配)则不转换)
- 请注意,下面仅检查了 / 的第三个字符,如果需要,可以使用更严格的检查。
:-
DROP TABLE IF EXISTS mytable;
CREATE TABLE IF NOT EXISTS mytable (mycolumn TEXT);
/* Load the testing data */
INSERT INTO mytable VALUES
('11/08/2019 00:00:00'),('01/08/2019 00:00:00'),('31/01/2019 00:00:00'),('31/01/2019 13:25:33.004') /* test for micro seconds (dropped by utc)*/;
/* display data before conversion */
SELECT * FROM mytable;
/* Convert the data to unix */
UPDATE mytable SET mycolumn =
CASE WHEN substr(mycolumn,1) = '/'
THEN
strftime('%s',8))
ELSE
mycolumn
END
;
/* Display data as is,as formatted localised and as formatted UTC */
SELECT *,datetime(mycolumn,'unixepoch','localtime') AS local,'unixepoch') AS utc FROM mytable;
-
请注意,上面的内容不适用于2019年1月1日之类的日期,此类日期将需要更复杂的CASE子句。
-
请注意,UTC是世界范围内的协调时间,即您存储的一个值是您根据时区从UTC调整的
结果
- 在+10小时的时区进行笔记测试
首次运行时的结果是:-
预转换:-
转化后
重新运行(DROP已被注释掉)
预转换(混合数据):-
- 圈出的数据已转换