SQLite Forum

Counting with datetime

Counting with datetime

(1) By Jussi1112 on 2020-09-22 14:49:30 [link] [source]

I've created a powershell script that count something and write this to an SQLite datebase, including the date and time at that moment. The date and time will be write in the following format: "yyyy/MM/dd HH:mm:ss" to the database

Now i want to create a query that , for example, show only records from the last 30 days.

If i select the date column it shows data, but when i try a select with "datetime" it shows only "NULL" as value.

select datetime ('date') => Give NULL ,date => Give data/output from table

What did i do wrong? And did anyone have a example?

(2) By Keith Medcalf (kmedcalf) on 2020-09-22 16:16:34 in reply to 1 [link] [source]

Your date format is invalid. You must replace the '/' with '-' in order for the date to be in a valid format.

A date compliant with the International Standard is YYYY-MM-DD not YYYY/MM/DD.

(3.1) By Keith Medcalf (kmedcalf) on 2020-09-22 16:28:50 edited from 3.0 in reply to 2 [link] [source]

So for your contrived example:

select datetime(replace(date, '/', '-'))

(6.1) By Keith Medcalf (kmedcalf) on 2020-09-22 16:27:28 edited from 6.0 in reply to 3.0 [link] [source]

So to find the dates within the last 30 days one might use the following where clause:

WHERE julianday() - julianday(replace(date, '/', '-')) <= 30

assuming that the date is UT1. If it is not UT1 but is rather localtime then you need to convert it to UT1 (UTC) and pray that the whim of politicians and the vagaries of Microsoft's inability to handle timezone and DST transitions does not bugger you up:

WHERE julianday() - julianday(replace(date, '/', '-'), 'utc') <= 30

NB: Both of the above assume that "date" does not contain a date in the future.

(7.1) By Keith Medcalf (kmedcalf) on 2020-09-23 01:12:57 edited from 7.0 in reply to 6.1 [link] [source]

Note that this assumes that the definition of a "day" is an interval of time and not the increment of one "date" to the next.

If by 30 days you mean the period (which may be more or less than 30 days) from 00:00:00 on the date which preceeds todays date by 30 days, then you need to calculate the limit differently, as in:

WHERE julianday(replace(date, '/', '-')) >= julianday('now', 'start of day', '-30 days')

assuming that "date" is in UT1 and that dates are UT1 dates and not local dates.

If "date" is in localtime and you want to use localtime dates then you would need:

WHERE julianday(replace(date, '/', '-'), 'utc') >= julianday('now', 'localtime', 'start of day', '+12 hours', '-30 days', 'start of day', 'utc')

It would generally be easier to compute the boundary stamp in your application and pass it as a parameter to the query then you can just use WHERE date >= ? and send the boundary as a parameter.

(8.1) By Keith Medcalf (kmedcalf) on 2020-09-23 01:20:33 edited from 8.0 in reply to 7.1 [source]

Note that this is only accurate if run at exactly midnight. If you run between 00:00:00.001 and 24:59:59.999 then it will be 30 days plus whatever part of today is elapsed so far.

(5) By Gunter Hick (gunter_hick) on 2020-09-22 16:19:55 in reply to 2 [link] [source]

That and the quotes are wrong.

(4) By Gunter Hick (gunter_hick) on 2020-09-22 16:18:24 in reply to 1 [link] [source]

You are using the wrong quotes.

'date' (single quotes) is a string composed of the letters d, a, t, e and NUL characters.

date (no quotes) is the name of a column.

"date" (double quotes) could be either, depending on context.

Try not to use quotes unless they are strictly necessary to avoid confusion.

(9) By Jussi1112 on 2020-09-25 11:23:14 in reply to 1 [link] [source]

Sorry for my late response. It has all worked out now. Thank you all for your fast and educational support.

select X ,date from table where datetime(date) NOTNULL AND datetime(date) >= datetime('now', '-2 hours')