SQL Dates and Time Formatting

Question

got a quick question on some SQL formatting. I am struggling figuring out how to make the formatting for a table the same. The table includes about 15 years worth of data. Most of the time stamps are formatted in a similar way. They are in columns "Day" "Date" and "YT":

"Sun" "Dec 17" " 2017 11:58:00 PM"

The data from this past year is formatted to look like this:

"Jan 4 - 10:43 AM"

This string is all in one column; "Day".

I am looking for a few things. I need the format to be the same so that I can work with it. So hoping I can leave the day column blank, since it doesn't tell us what day of the week it was. I would like to move the part of the string that is the month and date into column "Date" to match the previous data. and I would like to add "2018" before the time stamp and have that value be in column "YT".

I am using DB Browser and SQLite.

I appreciate all the help. I am new to sql and python and apologize if I have missed something obvious. If there is an easy solution that I am missing, some pointing me in the right direction to read about it/figure it out myself would also be greatly appreciated.

Answer 1

Assuming you are using sqlite:

I created a simple table to verify the results as follows:

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","","");

So, after committing the previous statements, running the following query...

SELECT * FROM MY_TABLE;

returns...

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

Finally, this is the big query to get all of your records with a nicely formatted datetime column. You'll have to modify this query to match your table name and column names.

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
;

Returns:

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

From here, you can get DATETIME_FULL in any format you'd like, see https://www.sqlite.org/lang_datefunc.html. Also, python has many ways to work with datetime data which you can search on your own.