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:

My goal:

Table 2

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);

CodePudding user response：

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.