sqlite regarding datetime query

Question

Help me solving this.

I have certain data stored in DB in milliseconds. But this was not the format I wanted so I converted it into actual date format. The format is getting updated as a new column during runtime. strftime(MillisField*1000,'%Y %m %d %H:%M:%s') as date.

This returns the actual date and time format in a new column.

But the problem here is if I want the last six months of some particular data, so I tried this:

MillisField > 2017-12-01

It didn't show me the last six months of data, but, if I try this:

MillisField > 1512066600000

it shows the exact result I wanted, but I don't want to give in milliseconds, I need it in date format.

Answer 1

2017-12-01 = 2004 i.e. without quotes is numeric so, 01 and 12 are subtracted from 2017 and hence the result is 2004 which is way less than 1512066600000.

'2017-12-01' is a string and would result in inconsistent results for comparison on it's own.

What you need to do is ensure that you are comparing like for like rather than the colloquial comparison of apples and oranges.

The following may assist in better understanding how you can compare and how you shouldn't

• two given dates 2017-12-01 (after date in millis to test against) and 2000-01-01 (before the data in millis to test against).

:-

SELECT
    date('2000-01-01') AS date001,
    date('2017-12-01') AS date002,

    datetime('2000-01-01') AS dattim001,
    datetime('2017-12-01') AS dattim002,

    strftime('%s','2000-01-01') AS secs_date001, --  946684800
    strftime('%s','2017-12-01') AS secs_date002, -- 1512086400 (2017-11-30 18:30:00)

    datetime(1512066600,'unixepoch') AS comparison_as_date,

-- Is 946684800 (2000-01-01) > 1512066600 (2017-11-30) (NO but!!)
    datetime('2000-01-01') > 1512066600 AS compare001, -- 1 = true = yes (WRONG)
    CAST(datetime('2000-01-01') AS INTEGER) > 1512066600 AS compare002, -- 0 = false = no  (CORRECT)
    datetime('2000-01-01') > datetime(1512066600,'unixepoch') AS compare003, -- 0 = false = no (CORRECT)
    strftime('%s','2000-01-01') > 1512066600 AS compare004, -- 1 = true = yes (WRONG)
    CAST(strftime('%s','2000-01-01') AS INTEGER) > 1512066600 AS compare005, -- 0 = false = no (CORRECT)

-- Is 1512086400 (2017-12-01) > 1512066600000 (2017-11-30) (YES but!!)
    datetime('2017-12-01') > 1512066600 AS compare101, -- 1 = true = yes (CORRECT)
    CAST(datetime('2017-12-01') AS INTEGER) > 1512066600 AS compare102, -- 0 = false = no  (WRONG) 
    datetime('2017-12-01') > datetime(1512066600,'unixepoch') AS compare103, -- 1 = true = yes (CORRECT)
    strftime('%s','2017-12-01') > 1512066600 AS compare104, -- 1 = true = yes (CORRECT)
    CAST(strftime('%s','2017-12-01') AS INTEGER) > 1512066600 AS compare105 -- 1 = true = yes (CORRECT)

Therefore :-

• compare001/101, compare002/102 and compare004/104 will not always return the correct results
• compare003/103 and compare005/105 methods are consistent

i.e.

datetime('2000-01-01') > datetime(1512066600,'unixepoch')

and

CAST(strftime('%s','2000-01-01') AS INTEGER) > 1512066600

Are reliable (i.e. they convert the different formats into compatible formats for comparison)

Results from the above SQL

The above produces (split into 2 for display) :-