How to deal with "YYYY-MM-DD HH:MM:SS.SSS 0000" format in SQLite?-CodePudding

I have two columns, DATE_A and DATE_B. I need to find how much time is between the two dates. Usually, I would use JULIANDAY() and subtract one date from another, but the output is null because of the " 0000" part.

Below you'll find an example of values contained in the two columns:

DATE_A - '2022-05-12 00:16:17.553 0000'

DATE_B - '2022-06-02 00:02:01.158 0000'

Please tell me what ' 0000' means and how can I find the time elapsed between the two dates.

CodePudding user response：

0000 is the offset from UTC this time represents in hours and minutes. For example, here in the US Pacific it's daylight savings time and we're 7 hours behind UTC so we're -0700. 2022-05-12 08:00:00 0000 and 2022-05-12 01:00:00-0700 are the same point in time.

SQLite will accept a slightly different format. There has to be the : separator between hours and minutes.

2022-05-12 00:16:17.553  00:00
                           ^

You'll have to change the format. Use your programming language's date and time functions.

See "Time Values" in SQLite Date and Time Functions for valid formats.