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

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

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

[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