时间戳

首页 > 解决方案 > SQL 日期和时间格式

sql - SQL 日期和时间格式

问题描述

有一个关于一些 SQL 格式的快速问题。我正在努力弄清楚如何使表格的格式相同。该表包括大约 15 年的数据。大多数时间戳都以类似的方式格式化。它们位于“Day”“Date”和“YT”列中：

“周日”“12 月 17 日”“2017 年晚上 11:58:00”

过去一年的数据格式如下：

“1 月 4 日 - 上午 10:43”

该字符串都在一列中；“天”。

我正在寻找一些东西。我需要格式相同，以便我可以使用它。所以希望我可以将日期列留空，因为它没有告诉我们它是星期几。我想将作为月份和日期的字符串部分移动到“日期”列中以匹配以前的数据。我想在时间戳之前添加“2018”，并将该值放在“YT”列中。

我正在使用数据库浏览器和 SQLite。

我感谢所有的帮助。我是 sql 和 python 的新手，如果我遗漏了一些明显的东西，我深表歉意。如果我缺少一个简单的解决方案，那么一些人指出我正确的方向来阅读它/自己弄清楚它也将不胜感激。

标签： sqlsqlitedatetimedb-browser-sqlite

解决方案

假设您使用的是 sqlite：

我创建了一个简单的表来验证结果如下：

DROP TABLE IF EXISTS MY_TABLE;
CREATE TABLE MY_TABLE ("SOME_UNIQUE_ID" INTEGER,"Day" TEXT,"Date" TEXT,"YT" TEXT);
INSERT INTO MY_TABLE ("SOME_UNIQUE_ID","Day","Date","YT") VALUES ("1","Sun","Dec 17","2017 11:58:00 PM");
INSERT INTO MY_TABLE ("SOME_UNIQUE_ID","Day","Date","YT") VALUES ("2","Jan 4 - 10:43 AM","","");

因此，在提交前面的语句后，运行以下查询......

SELECT * FROM MY_TABLE;

返回...

SOME_UNIQUEID Day                 Date      YT
1             Sun                 Dec 17    2017 11:58:00 PM
2             Jan 4 - 10:43 AM

最后，这是使用格式良好的日期时间列获取所有记录的大查询。您必须修改此查询以匹配您的表名和列名。

SELECT SOME_UNIQUE_ID, 
    datetime(FULL_DATE_STRING) AS DATETIME_FULL 
    FROM (
        SELECT SOME_UNIQUE_ID,
        coalesce(YYYY_str,"") || "-" || coalesce(MM_str,"") || "-" || coalesce(DD_str,"") || " " || coalesce(HHMMSS_str,"") AS FULL_DATE_STRING
        FROM (
            SELECT SOME_UNIQUE_ID,
                substr(YT,0,5) AS YYYY_str,
                CASE substr("Date",0,4) 
                   WHEN 'Jan' 
                       THEN '01' 
                   WHEN 'Feb' 
                       THEN '02'
                   WHEN 'Mar' 
                       THEN '03'
                   WHEN 'Apr' 
                       THEN '04'
                   WHEN 'May' 
                       THEN '05'
                   WHEN 'Jun' 
                       THEN '06'
                   WHEN 'Jul' 
                       THEN '07'
                   WHEN 'Aug' 
                       THEN '08'
                   WHEN 'Sep' 
                       THEN '09'
                   WHEN 'Oct' 
                       THEN '10'
                   WHEN 'Nov' 
                       THEN '11'
                   WHEN 'Dec' 
                       THEN '12'
                   ELSE NULL
                END MM_str,
                substr("Date",5,2) AS DD_str,
                substr(substr(YT, 6),0,9) AS HHMMSS_str
            FROM MY_TABLE
            )
    )
WHERE DATETIME_FULL IS NOT NULL
UNION ALL
SELECT SOME_UNIQUE_ID, 
    datetime(YYYYMMDD_str || " " || HHMMSS_str_mod) AS DATETIME_FULL 
FROM (
    SELECT SOME_UNIQUE_ID, 
        YYYY_str || "-" || MM_str || "-" || DD_str AS YYYYMMDD_str,
        CASE AMPM_str
            WHEN 'AM'
                THEN HHMMSS_str
            WHEN 'PM'
                THEN replace(HHMMSS_str,substr(HHMMSS_str,1,2),substr(HHMMSS_str,1,2)+12)
            ELSE NULL
        END HHMMSS_str_mod
    FROM (
        SELECT SOME_UNIQUE_ID,
            "2018" AS YYYY_str,
            MM_str,
            substr('00'||D_str,-2) AS DD_str,
            HHMMSS_str,
            AMPM_str
        FROM (
            SELECT SOME_UNIQUE_ID,
            CASE substr("Day",1,3) 
               WHEN 'Jan' 
                   THEN '01' 
               WHEN 'Feb' 
                   THEN '02'
               WHEN 'Mar' 
                   THEN '03'
               WHEN 'Apr' 
                   THEN '04'
               WHEN 'May' 
                   THEN '05'
               WHEN 'Jun' 
                   THEN '06'
               WHEN 'Jul' 
                   THEN '07'
               WHEN 'Aug' 
                   THEN '08'
               WHEN 'Sep' 
                   THEN '09'
               WHEN 'Oct' 
                   THEN '10'
               WHEN 'Nov' 
                   THEN '11'
               WHEN 'Dec' 
                   THEN '12'
               ELSE NULL
            END MM_str,
            rtrim(substr("Day",5,2)) AS D_str,
            substr("Day",-8,5) || ":00" AS HHMMSS_str,
            substr("Day",-2,2) AS AMPM_str
            FROM MY_TABLE
            )
        )
    )
WHERE DATETIME_FULL IS NOT NULL
;

回报：

SOME_UNIQUE_ID DATETIME_FULL
1              2017-12-17 11:58:00
2              2018-01-04 10:43:00

从这里，你可以得到你想要的任何格式的 DATETIME_FULL，见 https://www.sqlite.org/lang_datefunc.html。此外，python 有很多方法可以处理您可以自行搜索的日期时间数据。

推荐阅读