在sqlite中转换12小时到24小时的时间格式 [英] Converting 12 hour to 24 hour time format in sqlite

问题描述

假设我有一个名为'Importscript.sql'的脚本来创建一个表，并从一个名为'rawdata.txt'的文本文件填充它，如下所示：

<$ p $ CREATE TABLE rawdata（
timestamp text，
value REAL
）;
.separator，
.import'〜/文件路径/ rawdata.txt'rawdata

'rawdata.txt'文件如下所示：

  2012/4/14 3:50:09 PM，24.7 
 2012/4/14 3:51:09，24.2 
 2012/4/14 3:52:09，99.3 
  

然后在命令提示符下使用以下命令创建一个名为RAWDATA.db的数据库，并在其中包含rawdata表：

  sqlite3 RAWDATA.db< 〜/ path to scripts / importscript.sql 
  

导入完成后的表格看起来像这样：

 时间戳记值
 2012/4/14 3:50:09 24.7 
 4/14 / 2012 3:51:09 PM 24.2 
 2012/4/14 3:52:09 PM 99.3 
  

但我希望它看起来像这样

 时间戳记值
 2012/4/14 15:50 ：09 24.7 
 2012/4/14 15:51:09 24.2 
 2012/4/14 15:52:09 99.3 
  

如何将此时间戳列转换为24小时制？

为了使用SQLite日期/时间函数并允许日期比较（和排序），日期应该存储在 YYYY-MM-DD HH：MM-SS / p>

所以，我的答案会解决你的问题和这个日期转换：

pre $ UPDATE rawdata SET timestamp =（SELECT DATETIME（SUBSTR（timestamp，s2 + 1，4）|| '-01-01'，
SUBSTR（timestamp，1，s1 -1）||'months'，'-1 months'，
SUBSTR（timestamp，s1 + 1，s2-s1-1 ）||'days'，'-1 days'，
SUBSTR（timestamp，s3 + 1，s4-s3-1）||'hours'，
SUBSTR（timestamp，s4 + 1，s5 SUBSTR（时间戳，s5 + 1，s6-s5-1）||'秒'，
SUBSTR时间戳（时间戳，s3 + 1，s4 --s3-1）= '12'THEN'-12小时'ELSE'0小时'END，
SUBSTR时间（时间戳，s6 + 1）像'PM'那么'+12小时'其他'0小时'END
）
FROM（
SELECT *，
INSTR（SUBSTR（timestamp，s1 + 1），'/'）+ s1 AS s2，
INSTR SUBSTR（timestamp，s4 + 1），'：'）+ s4 AS s5，
INSTR（SUBSTR（timestamp，s3 + 1），''）+ s3 AS s6
FROM（
SELECT timestamp，
INSTR（timestamp，'/'）AS s1，
INSTR（timestamp，''）AS s3，
INSTR（timestamp，'：'）AS s4
）
））;


这个可怕的块得到所有分隔符的位置，从组件中重建日期并处理 PM 。注意 1 ... 11AM => +0 和 1 ... 11PM => +12 ，但是 12AM => -12 和 12PM => +0 ！

在 SQL小提琴。

Say I have a script named 'importscript.sql' to create a table and populate it from a text file named 'rawdata.txt' like so:

    CREATE TABLE rawdata (
    timestamp text,
    value REAL
    );
    .separator ,
    .import '~/path of the file/rawdata.txt' rawdata

The 'rawdata.txt' file looks like this:

4/14/2012 3:50:09 PM,24.7
4/14/2012 3:51:09 PM,24.2
4/14/2012 3:52:09 PM,99.3

Then creating a database named RAWDATA.db with the rawdata table in it in the command prompt using:

sqlite3 RAWDATA.db < ~/path to the script/importscript.sql

The table when the import is complete will look like this:

timestamp              value    
4/14/2012 3:50:09 PM   24.7
4/14/2012 3:51:09 PM   24.2
4/14/2012 3:52:09 PM   99.3

But I want it to look like this

timestamp              value
4/14/2012 15:50:09      24.7
4/14/2012 15:51:09      24.2
4/14/2012 15:52:09      99.3

How do I convert this timestamp column into a 24 hour format?

解决方案

In order to make use of SQLite date/time functions and allow date comparisons (and sorting), date should be stored in YYYY-MM-DD HH:MM-SS.

So, my answer will address your question and this date conversion:

UPDATE rawdata SET timestamp=(SELECT DATETIME(SUBSTR(timestamp, s2+1, 4)||'-01-01',
  SUBSTR(timestamp,    1, s1   -1)||' months', '-1 months',
  SUBSTR(timestamp, s1+1, s2-s1-1)||' days', '-1 days',
  SUBSTR(timestamp, s3+1, s4-s3-1)||' hours',
  SUBSTR(timestamp, s4+1, s5-s4-1)||' minutes',
  SUBSTR(timestamp, s5+1, s6-s5-1)||' seconds',
  CASE WHEN SUBSTR(timestamp, s3+1, s4-s3-1) = '12' THEN '-12 hours' ELSE '0 hours' END,
  CASE WHEN SUBSTR(timestamp, s6+1) LIKE 'PM' THEN '+12 hours' ELSE '0 hours' END
)
FROM (
  SELECT *,
    INSTR(SUBSTR(timestamp, s1+1), '/')+s1 AS s2,
    INSTR(SUBSTR(timestamp, s4+1), ':')+s4 AS s5,
    INSTR(SUBSTR(timestamp, s3+1), ' ')+s3 AS s6
  FROM (
    SELECT timestamp,
      INSTR(timestamp, '/') AS s1, 
      INSTR(timestamp, ' ') AS s3, 
      INSTR(timestamp, ':') AS s4
  )
));

This horrible block get all separators position, reconstruct date from components and handle PM. Note that 1...11AM => +0 and 1...11PM => +12, but 12AM => -12 and 12PM => +0!

Check it in SQL Fiddle.

这篇关于在sqlite中转换12小时到24小时的时间格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！