I'm not quite sure on that first update statement, seems like you're not using
it right.
Otherwise though I think I'd recommend storing things as text "YYYY-MM-DD
HH:MM:SS" as that's the input type for strftime, which is what would be used
for the queries. It also doesn't include the time zone, which is also what you
were looking for. (Or at least I think it assumes everything is GMT)
SELECT count(*) FROM Events WHERE strftime('%m%d', eventTime) = '0103';--Jan 3rd
SELECT count(*) FROM Events WHERE strftime('%w', eventTime) = '4';--Friday
SELECT count(*) FROM Events WHERE strftime('%H%M', eventTime) between '1000'
and '1014';--10:00 up to 10:15
Heck, with that standardized text format you could even use LIKE to do a few of
those.
...WHERE eventTime LIKE '____-01-03%';
-----Original Message-----
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
Behalf Of Jeffrey Mattox
Sent: Tuesday, January 10, 2017 10:02 AM
To: SQLite mailing list
Subject: [sqlite] Advice on using dates and hours for bin counts, time-zone
agnostic
My application will be counting events and saving the totals for every
15-minute time period over many days -- that table will have an eventCount and
a date/time for each bin. I'll be displaying the data as various histograms:
the number of events per day over the year (365 values), the number of events
for every day of the week (7 values), the number of events for each quarter
hour of every day (24*7 values), etc.
Pseudo SQL:
UPDATE Events SET count = count + 1 WHERE eventTime =
integer(now_minutes/15)
SELECT TOTAL(count) FROM Events WHERE eventTime is Jan 3 of every year
SELECT TOTAL(count) FROM Events WHERE eventTime is a Friday
SELECT TOTAL(count) FROM Events WHERE eventTime is between 10:00 am and
10:15 am
etc.
How should I define "eventTime" to facilitate the storage and retrieval of the
counts? E.g., one field or a pair of fields (the date and the
15-minute-period)? I'm unsure whether to use date and time, or datetime, or
julianday.
One snake pit I want to avoid is timezone hassle. Everything should be based
on the local clock time, regardless of where the computer is located later. If
I store a value for "1/1/17 at noon" when I'm in the eastern TZ and later ask
for the value at "1/1/17 at noon" when I'm in any other time zone, I want the
same result regardless of the time zone -- as if it's always the same date and
time everywhere. (I've handled this in the past by converting the local time
to a string, and saving that without any TZ indicator.)
How should I define the columns for date and time?
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
