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
