Filter SQlite unix timestamp data on hh:mm:ss part-CodePudding

I have the following records in my SQlite DB.

      **SESSION**                     **DATETIME    **BID**    ASK**
25e309b5-eecd-49f7-a1e4-0a77d4804978    1650240126  1979.74 1979.99
25e309b5-eecd-49f7-a1e4-0a77d4804978    1650240129  1979.73 1979.97
25e309b5-eecd-49f7-a1e4-0a77d4804978    1650240129  1979.73 1979.92
25e309b5-eecd-49f7-a1e4-0a77d4804978    1650240129  1979.7  1979.92
25e309b5-eecd-49f7-a1e4-0a77d4804978    1650240130  1979.68 1979.91
25e309b5-eecd-49f7-a1e4-0a77d4804978    1650240130  1979.68 1979.89

My goal is to extract the hh:mm:ss time from the DATETIME field and to filter it. It should look some think like that

SELECT * from table where strftime('%H:%M:%S',Datetime) < '00:02:05'

The problem with this query is that it's not working.

Is there a way to extract the part from the unix time and compare it to my custom one ?

CodePudding user response：

You need the function time() with the 'unixepoch' modifier:

SELECT * 
FROM tablename 
WHERE time(DATETIME, 'unixepoch') < '00:02:05';