2

I have been trying with no success to to count how many values were created in a specific week day:

SELECT count(*) as count FROM packets WHERE strftime("%w", timeIn) = '1';

I have this values in timeIn

1472434822.60033
1472434829.12632
1472434962.34593

I don't know what I am doing wrong here.

furthermore, if I use this:

SELECT count(*) as count FROM packets WHERE strftime("%w", timeIn) = '6';

I get

2

which makes no sense. Thank you in advance.

Joe
  • 234
  • 1
  • 10

1 Answers1

1

You appear to be storing the date as the number of seconds since 1970 (the Unix epoch) - a common representation. The time strings accepted by the SQLite date functions (see the Time Strings section) default to interpreting numeric time strings as a Julian day numbers:

Similarly, format 12 is shown with 10 significant digits, but the date/time functions will really accept as many or as few digits as are necessary to represent the Julian day number.

You can see this with the following SELECT:

SELECT strftime('%Y-%m-%d', 1472428800.6) AS t

the result of which is:

4026-48-26

For your date representation to be interpreted as a Unix epoch, you need to include 'unixepoch' in the strftime call:

SELECT strftime('%Y-%m-%d', 1472428800.6, 'unixepoch') AS t

which returns:

2016-08-29

If you modify your SELECT to be:

SELECT count(*) as count FROM packets WHERE strftime("%w", timeIn, 'unixepoch') = '6'

you should see results more inline with your expectations.

cartant
  • 57,105
  • 17
  • 163
  • 197
  • Thank you. I tried but I am not getting any returns: `SELECT count(*) as count FROM packets WHERE strftime("%w", timeIn, 'unixepoch') = 1;` should it be better to change the format form the DB instead? – Joe Aug 29 '16 at 03:24
  • You could experiment with some `SELECT` statements to make sure the conversions are what you expect. For example, using the first value from your question: `SELECT strftime('%w', 1472434822.60033, 'unixepoch') AS w, date(1472434822.60033, 'unixepoch') AS d` returns `1` and `2016-08-29`. – cartant Aug 29 '16 at 03:28
  • thank you again for your kind response. I am getting `1` and `2016-08-29` respectively. Oddly though, `SELECT count(*) as count FROM packets WHERE strftime("%w", timeIn, 'unixepoch') = '0'` is returning `3`. Monday should be one ey? – Joe Aug 29 '16 at 03:49
  • If you are continuing to have problems, I would suggest using the `sqlite3` command line (or GUI tool like SqliteSpy) to run some `SELECT` commands on your data - to make sure it is what you think it is. Regarding using a different date format, there is not much point, but if you have a large amount of data, you might want to include a column that has a pre-caclulated day-of-the-week (calculate it with `strftime` on `INSERT`) so that the `strftime` does not have to be applied to every row when querying. – cartant Aug 29 '16 at 03:49
  • From the [documentation](https://www.sqlite.org/lang_datefunc.html): "%w" is the "day of week 0-6 with Sunday==0" – cartant Aug 29 '16 at 03:51
  • Thank you! Now is working like a charm! thank you for taking your time here. apreciated. – Joe Aug 29 '16 at 04:05