Re: [sqlite] Advice on using dates and hours for bin counts, time-zone agnostic
I agree with Dave Raymond, I would use "-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 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
Re: [sqlite] Advice on using dates and hours for bin counts, time-zone agnostic
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 "-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
Re: [sqlite] Advice on using dates and hours for bin counts, time-zone agnostic
On 10 Jan 2017, at 3:01pm, Jeffrey Mattox 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
[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