I agree with Dave Raymond, I would use "YYYY-MM-DD HH:MM:SS" format because it is both sortable and comparable as well as an ISO standard (ISO 8601). In addition, you don't have to know the base date the number of days were calculated since. The base date (epoch) for MS Excel Windows, MS Excel Mac and MS Access are all different. Besides it is human readable and you can eyeball if the string seams reasonable.
Are you using just one datetime for each interval? or are you using start and end times? Are the intervals GUARANTEED to be 15 minutes? Really? Has everyone agreed whether the timestamp is the start or end time? What reporting applications (besides SQLite) will be used? (for example, is anyone going to produce charts?) How do these applications represent time? How and when are the times synchronized (is it an automatic process? does it require human intervention? is it performed manually?) National Institute of Standards and Technology (NIST) Time http://www.time.gov/ Ideally, you would like to store the location, timezone and a delta (hours) from GMT. Time Zone database http://www.iana.org/time-zones Time Zone details http://www.iana.org/time-zones/repository/tz-link.html Some best practices https://www.w3.org/TR/timezone/ But, as Einstein said, it's all relative. Jim Callahan Orlando, FL On Tue, Jan 10, 2017 at 10:01 AM, Jeffrey Mattox <j...@mac.com> wrote: > 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
