Re: [sqlite] Advice on using dates and hours for bin counts, time-zone agnostic

[Jim Callahan]

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

[David Raymond]

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

[Simon Slavin]

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