SQLite strftime() weekday -

i have been trying no success to count how many values created in specific week day:

select count(*) count packets strftime("%w", timein) = '1'; 

i have values in timein

1472434822.60033 1472434829.12632 1472434962.34593 

i don't know doing wrong here.

furthermore, if use this:

select count(*) count packets strftime("%w", timein) = '6'; 

i get

2 

which makes no sense. thank in advance.

you appear storing date number of seconds since 1970 (the unix epoch) - common representation. time strings accepted sqlite date functions (see time strings section) default interpreting numeric time strings julian day numbers:

similarly, format 12 shown 10 significant digits, date/time functions accept many or few digits necessary represent julian day number.

you can see following select:

select strftime('%y-%m-%d', 1472428800.6) t 

the result of is:

4026-48-26 

for date representation interpreted unix epoch, need include 'unixepoch' in strftime call:

select strftime('%y-%m-%d', 1472428800.6, 'unixepoch') t 

which returns:

2016-08-29 

if modify select be:

select count(*) count packets strftime("%w", timein, 'unixepoch') = '6' 

you should see results more inline expectations.