On 10 Jan 2017, at 3:01pm, Jeffrey Mattox <j...@mac.com> wrote: > 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.
Because of the various different things you may base your requests on … Day of the year Day of week Time of day I think you may need to save multiple time/date columns. It can be extremely difficult to express a concept like "Every January 3rd" when all you have is a simple timestamp. So I would be saving /all/ the following columns: date-as-text, unixepoch, Julian Day, day-of-month, month, year, weekday, timezone Depending on your search you may select using one or more of the above. > 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, Which local clock ? Which computer ? If the computer is not in the same timezone as the event, how does it know the timezone of the event ? Does 'January 3rd' mean that date in the locality of the event or in the locality of the person doing the search ? > I want the same result regardless of the time zone -- as if it's always the > same date and time everywhere. The normal way to do that is to standardise all timestamps as if they happened in UTC (what used to be thought of as Timezone +00, Greenwich Mean Time). Friday here can be Thursday there. In fact, if you allow for Summer Time adjustments, it can be Tuesday there. When you’re scanning for "WHERE eventTime is a Friday" you need to know what 'Friday' means. There are two ways I see this handled: The first is to save everything with a timezone and when you do your searching, compensate for it. The second is to convert all event times to UTC before any storage, but to save the event’s timezone as well as saving the UTC timestamp. Depending on how much flexibility you need to support in your queries, you may need to save all of these timezone, date-as-text, unixepoch, Julian Day, day-of-month, month, year, weekday, time for both the local time of the event, and for that datetime converted to UTC. This means you end up recording 15 different columns. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
