You query is incorrect. It should be:
SELECT CASE
WHEN (unix10and13.dt < 10000000000)
THEN DateTime(unix10and13.dt, 'unixepoch')
WHEN (unix10and13.dt > 10000000000)
THEN DateTime(unix10and13.dt / 1000, 'unixepoch')
ELSE dt
END AS converted
FROM unix10and13;
When your case, you are using the CASE <var> WHEN <value> ...
So, the THEN clauses are comparing the value of DT to the result on (dt <
10000000000) or (dt > 10000000000). The results of the expression are always 1
or 0, which never equals DT, so the THEN clause is never executed and the ELSE
is always taken.
> -----Original Message-----
> From: sqlite-users [mailto:[email protected]]
> On Behalf Of Paul Sanderson
> Sent: Thursday, 29 September, 2016 07:14
> To: General Discussion of SQLite Database
> Subject: [sqlite] converting unix10 and unix13 dates in the same column
>
> I have a table with dates in different formats, either 10 digit or 13
> digit unix dates
>
> 1234345087123
> 1234567890
> 1432101234
> 1456754323012
>
> I want a sql query that will convert both dates, I tried this
>
> SELECT CASE dt
> WHEN (unix10and13.dt < 10000000000)
> THEN DateTime(unix10and13.dt, 'unixepoch')
> WHEN (unix10and13.dt > 10000000000)
> THEN DateTime(unix10and13.dt / 1000, 'unixepoch')
> ELSE dt
> END AS converted
> FROM unix10and13
>
> But this returns the original values - i.e. the else portion is being
> evaluated but one of the previous expressions should evaluate to true
> surely? Any ideas why this is failing?
>
>
>
> I am also interested (because I tried and failed) in coding a second
> query that would return all four rows but in two columns each with
> either a unix10 or 13 date in the correct column - something like
> this:
>
> unix10, unix13
> ,1234345087123
> 1234567890,
> 1432101234,
> ,1456754323012
>
> any suggestions to achieve this approach?
>
>
>
> Paul
> www.sandersonforensics.com
> skype: r3scue193
> twitter: @sandersonforens
> Tel +44 (0)1326 572786
> http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-
> Toolkit
> -Forensic Toolkit for SQLite
> email from a work address for a fully functional demo licence
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
