SQLite User Forum

4/17/2022, 5:03:31 PM convert into YYYY-MM-DD HH:MM:SS
Login
Chronological

4/17/2022, 5:03:31 PM convert into YYYY-MM-DD HH:MM:SS

(1) By Aj (aj_123) on 2022-05-03 10:50:16 [link] [source]

In my SQLite table, I have two columns jointime and leavetime and both column’s values store ‘4/17/2022, 5:03:31 PM’ format and want to convert into ‘2022-04-17 17:03:31’ SQLite date format IN ANOTHER SQLite table name teacher

(2) By Gunter Hick (gunter_hick) on 2022-05-03 11:13:30 in reply to 1 [link] [source] 

Start your research for this suspected homework assignment here: https://sqlite.org/lang_datefunc.html

(3) By MBL (UserMBL) on 2022-05-04 07:50:17 in reply to 1 [link] [source]

One possible solution may look like my following example:

Using replace function calls to separate the fields into a format which can be converted into a valid json array.

Using the json array fields to go into a time function to allow for the +12 hours respective +0 hours modifier for the PM or AM postfix.

D:\Sandbox>sqlite3.exe
SQLite version 3.38.3 2022-04-27 12:03:15
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .mode qbox
sqlite> with AMPM(dt) as (
   ...>   values('4/17/2022, 5:03:31 PM')
   ...>        ,('4/17/2022, 5:03:31 AM')
   ...> )
   ...> , dtItems(dt,dtJSON) as (
   ...>   select dt, json('['||replace(replace(replace(replace(replace(replace(
   ...>          dt,'/',','),':',','),' PM',',12'),', ',','),',0',','),' AM',',0')||']')
   ...>     from AMPM
   ...> ), dtText(dtInput,dtJSON,dtISO) as (
   ...>   select *, printf('%s-%02d-%02d %02d:%02d:%02d',
   ...>              dtJSON->>'$[2]',0+dtJSON->>'$[0]',0+dtJSON->>'$[1]',
   ...>              0+dtJSON->>'$[3]',0+dtJSON->>'$[4]',0+dtJSON->>'$[5]')
   ...>     from dtItems
   ...> )
   ...> select *
   ...> , strftime('%Y-%m-%dT%H:%M:%SZ',dtISO,'+'||(dtJSON->>'$[6]')||' hours') dtFinalOutput
   ...>   from dtText;
┌─────────────────────────┬─────────────────────────┬───────────────────────┬────────────────────────┐
│         dtInput         │         dtJSON          │         dtISO         │      dtFinalOutput     │
├─────────────────────────┼─────────────────────────┼───────────────────────┼────────────────────────┤
│ '4/17/2022, 5:03:31 PM' │ '[4,17,2022,5,3,31,12]' │ '2022-04-17 05:03:31' │ '2022-04-17T17:03:31Z' │
│ '4/17/2022, 5:03:31 AM' │ '[4,17,2022,5,3,31,0]'  │ '2022-04-17 05:03:31' │ '2022-04-17T05:03:31Z' │
└─────────────────────────┴─────────────────────────┴───────────────────────┴────────────────────────┘
sqlite>

(4.1) By ddevienne on 2022-05-04 09:07:17 edited from 4.0 in reply to 3 [source]

Interesting use of JSON as an accumulation buffer, and the terse new ->> operator to access the JSON array elements.

Points for creativity and cleverness :) (small issue found by Keith notwithstanding)

Update: Oh, and on the first read, I missed the 0+ trick, as a terse alternative to cast(... as integer).

(5) By Keith Medcalf (kmedcalf) on 2022-05-04 08:59:23 in reply to 4.0 [link] [source]

Unfortunately, it is incorrect. It does not handle the AM/PM adjustments properly. The (correct) rule would add 12 hours for PM only if the hour is less than 12, and adds 12 hours for AM if the hours is less than 1.

sqlite> with AMPM(dt) as
   ...>      (
   ...>         values ('4/17/2022, 5:03:31 PM'),
   ...>                ('4/17/2022, 5:03:31 AM'),
   ...>                ('4/17/2022, 12:00:00 AM'),
   ...>                ('4/17/2022, 12:00:00 PM')
   ...>      ),
   ...>      dtItems(dt,dtJSON) as
   ...>      (
   ...>         select dt,
   ...>                json('['||replace(replace(replace(replace(replace(replace(
   ...>                     dt,'/',','),':',','),' PM',',12'),', ',','),',0',','),' AM',',0')||']')
   ...>           from AMPM
   ...>      ),
   ...>      dtText(dtInput,dtJSON,dtISO) as
   ...>      (
   ...>         select *,
   ...>                printf('%s-%02d-%02d %02d:%02d:%02d',
   ...>                         dtJSON->>'$[2]',0+dtJSON->>'$[0]',0+dtJSON->>'$[1]',
   ...>                         0+dtJSON->>'$[3]',0+dtJSON->>'$[4]',0+dtJSON->>'$[5]')
   ...>           from dtItems
   ...>      )
   ...> select *,
   ...>       strftime('%Y-%m-%dT%H:%M:%SZ',dtISO,'+'||(dtJSON->>'$[6]')||' hours') dtFinalOutput
   ...>  from dtText
   ...> ;
┌──────────────────────────┬─────────────────────────┬───────────────────────┬────────────────────────┐
│         dtInput          │         dtJSON          │         dtISO         │     dtFinalOutput      │
├──────────────────────────┼─────────────────────────┼───────────────────────┼────────────────────────┤
│ '4/17/2022, 5:03:31 PM'  │ '[4,17,2022,5,3,31,12]' │ '2022-04-17 05:03:31' │ '2022-04-17T17:03:31Z' │
│ '4/17/2022, 5:03:31 AM'  │ '[4,17,2022,5,3,31,0]'  │ '2022-04-17 05:03:31' │ '2022-04-17T05:03:31Z' │
│ '4/17/2022, 12:00:00 AM' │ '[4,17,2022,12,0,0,0]'  │ '2022-04-17 12:00:00' │ '2022-04-17T12:00:00Z' │
│ '4/17/2022, 12:00:00 PM' │ '[4,17,2022,12,0,0,12]' │ '2022-04-17 12:00:00' │ '2022-04-18T00:00:00Z' │
└──────────────────────────┴─────────────────────────┴───────────────────────┴────────────────────────┘

(6.1) By MBL (UserMBL) on 2022-05-05 06:52:12 edited from 6.0 in reply to 5 [link] [source]

Now, is my following adapted version of SQL correct? I am not used to american 12 hour time format, in my timezone we use format '%d.%m.%Y %H:%M:%S' with simple 24 hour range. Midnight is 00:00:00 and noon is 12:00:00, very easy - same as ISO-8601 is using it.

I added 2 more columns to make the expectation verifiable even before generated results are shown in addition (Seconds are used as enumerator).

D:\Sandbox>sqlite3.exe
SQLite version 3.38.3 2022-04-27 12:03:15
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .mode box
sqlite> with AMPM(dt,dtExpected,Rule) as (
   ...>   values('4/17/2022, 5:03:31 AM'  ,'2022-04-17T05:03:31Z','')
   ...>        ,('4/17/2022, 5:03:31 PM'  ,'2022-04-17T17:03:31Z','add 12 hours for PM only if the hour is less than 12')
   ...>
   ...>        ,('4/17/2022, 11:30:02 AM' ,'2022-04-17T11:30:02Z','')
   ...>        ,('4/17/2022, 12:30:03 PM' ,'2022-04-17T12:30:03Z','Noon: hour for PM is NOT less than 12')
   ...>        ,('4/17/2022, 01:30:04 PM' ,'2022-04-17T13:30:04Z','add 12 hours for AM if the hour is less than 1')
   ...>
   ...>        ,('4/17/2022, 11:30:05 PM' ,'2022-04-17T23:30:05Z','add 12 hours for PM only if the hour is less than 12')
   ...>        ,('4/18/2022, 12:30:06 AM' ,'2022-04-18T00:30:06Z','Night: hours for AM are NOT less than 1')
   ...>        ,('4/18/2022, 01:30:07 AM' ,'2022-04-18T01:30:07Z','')
   ...>
   ...>        ,('7/6/2021, 22:42:08 PM','2021-07-06T22:42:08Z','24 hours format (PM optional)')
   ...> )
   ...> , dtItems(dt,dtJSON,dtExpected,Rule) as (
   ...>   select dt, json('[' || replace( replace( replace( replace( replace( replace(
   ...>                dt,'/',','),':',','),' PM',',12'),', ',','),',0',','),' AM',',0') || ']')
   ...>            , dtExpected,Rule                                                    from AMPM
   ...> ), dtText(dtInput,dtJSON,dtExpected,Rule,dtISO) as (
   ...>   select *, printf('%s-%02d-%02d %02d:%02d:%02d', dtJSON->>'$[2]',0+dtJSON->>'$[0]',0+dtJSON->>'$[1]'
   ...>                  , 0+dtJSON->>'$[3]',0+dtJSON->>'$[4]',0+dtJSON->>'$[5]')       from dtItems
   ...> )
   ...> select dtInput, dtJSON, dtExpected
   ...>   , strftime('%Y-%m-%dT%H:%M:%SZ',dtISO,
   ...>      case when 0+(dtJSON->>'$[6]')=12 and 0+dtJSON->>'$[3]' >= 12 then '+0'
   ...>           when 0+(dtJSON->>'$[6]')=0  and 0+dtJSON->>'$[3]' = 12 then '-12'
   ...>           else '+'||(dtJSON->>'$[6]') end   ||' hours') dtFinalOutput  , Rule   from dtText;
┌────────────────────────┬────────────────────────┬──────────────────────┬──────────────────────┬──────────────────────────────────────────────────────┐
│        dtInput         │         dtJSON         │      dtExpected      │    dtFinalOutput     │                         Rule                         │
├────────────────────────┼────────────────────────┼──────────────────────┼──────────────────────┼──────────────────────────────────────────────────────┤
│ 4/17/2022, 5:03:31 AM  │ [4,17,2022,5,3,31,0]   │ 2022-04-17T05:03:31Z │ 2022-04-17T05:03:31Z │                                                      │
│ 4/17/2022, 5:03:31 PM  │ [4,17,2022,5,3,31,12]  │ 2022-04-17T17:03:31Z │ 2022-04-17T17:03:31Z │ add 12 hours for PM only if the hour is less than 12 │
│ 4/17/2022, 11:30:02 AM │ [4,17,2022,11,30,2,0]  │ 2022-04-17T11:30:02Z │ 2022-04-17T11:30:02Z │                                                      │
│ 4/17/2022, 12:30:03 PM │ [4,17,2022,12,30,3,12] │ 2022-04-17T12:30:03Z │ 2022-04-17T12:30:03Z │ Noon: hour for PM is NOT less than 12                │
│ 4/17/2022, 01:30:04 PM │ [4,17,2022,1,30,4,12]  │ 2022-04-17T13:30:04Z │ 2022-04-17T13:30:04Z │ add 12 hours for AM if the hour is less than 1       │
│ 4/17/2022, 11:30:05 PM │ [4,17,2022,11,30,5,12] │ 2022-04-17T23:30:05Z │ 2022-04-17T23:30:05Z │ add 12 hours for PM only if the hour is less than 12 │
│ 4/18/2022, 12:30:06 AM │ [4,18,2022,12,30,6,0]  │ 2022-04-18T00:30:06Z │ 2022-04-18T00:30:06Z │ Night: hours for AM are NOT less than 1              │
│ 4/18/2022, 01:30:07 AM │ [4,18,2022,1,30,7,0]   │ 2022-04-18T01:30:07Z │ 2022-04-18T01:30:07Z │                                                      │
│ 7/6/2021, 22:42:08 PM  │ [7,6,2021,22,42,8,12]  │ 2021-07-06T22:42:08Z │ 2021-07-06T22:42:08Z │ 24 hours format (PM optional)                        │
└────────────────────────┴────────────────────────┴──────────────────────┴──────────────────────┴──────────────────────────────────────────────────────┘
sqlite>