[FIXED] How to convert 0:00:00 to 00:00:00 time format in SQLite?

Issue

I am trying to convert time format 0:00:00 to 00:00:00 from my CSV file in SQLite. I have tried using time() but it would delete a time with any hour of a single digit (for example: 8:09:09 would be deleted, but the time frame between 10:00:00-23:59:59 is kept). Is there a way to convert this time frame in SQLite or would it be easier to convert it in my CSV file? How would I convert the time format in my CSV file, if applicable? I hope this question is clear as I am new here. I posted my table and the code here to illustrate the issue I am facing.

Table 1:

ID	BEFORE_DATE	BEFORE_TIME	AFTER_DATE	AFTER_TIME
81	2020-01-03	18:01:09	2020-01-03	22:44:12
8	2020-05-09	8:01:09	2020-05-09	13:44:12
9	2020-02-09	16:09:23	2020-02-09	13:00:00

My goal:

Table 2

ID	BEFORE_DATE	BEFORE_TIME	AFTER_DATE	AFTER_TIME
81	2020-01-03	18:01:09	2020-01-03	22:44:12
8	2020-05-09	8:01:09	2020-05-09	13:44:12

The problem here is that ID 8 won't show. Instead, only ID 81 shows.

My current code:

CREATE TABLE table2 AS
from table1
SELECT *
FROM table1
WHERE BEFORE_DATE=AFTER_DATE AND TIME(BEFORE_TIME) < TIME(AFTER_TIME);

Solution

If the problem is only with the missing 0 from the hours part, you may check the length of the time string and add that missing 0 for times with length =7, try the following:

With Fixed_Times AS
(
  SELECT ID, BEFORE_DATE,
       Case When length(BEFORE_TIME)=7 Then ('0' || BEFORE_TIME) Else BEFORE_TIME End AS BEFORE_TIME,
       AFTER_DATE,
       Case When length(AFTER_TIME)=7 Then ('0' || AFTER_TIME) Else AFTER_TIME End AS AFTER_TIME
  FROM table1
)

SELECT * FROM Fixed_Times
WHERE BEFORE_DATE <= AFTER_DATE AND BEFORE_TIME < AFTER_TIME

See a demo from db<>fiddle.

To create another table and insert the results to it (as you did in your question), try the following:

CREATE TABLE table2 AS
With Fixed_Times AS
(
  SELECT ID, BEFORE_DATE,
       Case When length(BEFORE_TIME)=7 Then ('0' || BEFORE_TIME) Else BEFORE_TIME End AS BEFORE_TIME,
       AFTER_DATE,
       Case When length(AFTER_TIME)=7 Then ('0' || AFTER_TIME) Else AFTER_TIME End AS AFTER_TIME
  FROM table1
)
SELECT * FROM Fixed_Times
WHERE BEFORE_DATE <= AFTER_DATE AND BEFORE_TIME < AFTER_TIME;

See a demo.

To update the original table (table1) and fix the time format, try the following:

Update table1 set BEFORE_TIME =
       Case When length(BEFORE_TIME)=7 Then ('0' || BEFORE_TIME) Else BEFORE_TIME End,
       AFTER_TIME =
       Case When length(AFTER_TIME)=7 Then ('0' || AFTER_TIME) Else AFTER_TIME End;

See a demo.

Answered By - Ahmed

Answer Checked By - Terry (PHPFixing Volunteer)