update or convert a date time value in a sqlite database

Question

I have a SQLite database with a TEXT column that stores a DateTime value formated like this: '11/08/2019 00:00:00'. I would like to convert the entire column contents to UTC Epoch timestamp local timezone. Is there an Update SQL string with a DateTime function that I could run using supported SQL syntax to perform this task or should I perhaps just write a quick C# console application to do it? I have not found any example online or in SO that would do what I need to do in this situation.

Answer 1

An UPDATE SQL such as :-

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
; 

could be used.

Example

Perhaps consider the following which will convert the column (or not if it has already been converted (or not if it does not match the dd/mm/yyyy format))

• Note the below just checks the 3rd character for /, a more rigorous check could be used if desired.

:-

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,3,1) = '/' 
        THEN
            strftime('%s',substr(mycolumn,7,4)||'-'||substr(mycolumn,4,2)||'-'||substr(mycolumn,1,2)||' '||substr(mycolumn,12,8))
        ELSE
            mycolumn
        END
    ;
    /* Display data as is, as formatted localised and as formatted UTC */
SELECT *, datetime(mycolumn,'unixepoch','localtime') AS local, datetime(mycolumn,'unixepoch') AS utc FROM mytable;

• Note the above would NOT cater for dates such as 1/1/2019, such dates would need a more complex CASE clause.

• Note that UTC is worldwide coordinated time i.e one value is stored you adjust from UTC according to the time zone

Results

• Note testing in timezone that is +10 hours

When first run the results are :-

Pre-conversion :-

Post-convserion

Rerun (DROP commented out)

Pre-conversion (mixed data) :-

• circled data is already converted

Post-conversion :-