On 2011-04-21, at 17:13, H. Phil Duby wrote: > [...] > With possible 'adjustments' for switching to daylight savings time and > back, depending on what timezone the original data is stored in. If > it was UTC, then no problem. But if it was in you local timezone, that > used daylight savings time, then daylight savings time changes will > cause a 1 hour gap, and a 1 hour overlap in the data once a year > [each].
That is one of the main reasons why database timestamps should be recorded only in UTC and placed in a column with UTC in its name. SQLite date and time functions do not support the full syntax of ISO 8601 (why should they), but a column may usefully be assigned a default value of CURRENT_TIMESTAMP, which will be UTC if the operating system time is synchronized via an NTP or SNTP client. I've only ever needed to store timestamps as integer unix timestamps because it reduces both data bandwidth and CPU overhead, and makes finding "not logged records" quite easy. This is just from memory so it's just the essence of a possible solution, but it's well worth refining while designing temporal database systems: 1. Create and populate "ReferenceTable" having a column "r" with all expected time entries. Once per minute would store the values of unix_timestamp DIV 60 (obviously each row increments by 1 up until the current moment or the last expected database entry). 2. ALTER TABLE LoggedData ADD COLUMN r INTEGER DEFAULT 0 3. UPDATE LoggedData SET r = function_returning_unix_timestamp(TimestampColumn) DIV 60 WHERE r=0 Note DIV means integer division: either add a user defined function or synthesize it from built-in functions. 4. SELECT function_returning_UTC_string(ReferenceTable.r * 60) FROM ReferenceTable LEFT OUTER JOIN LoggedData USING (r) WHERE LoggedData.r IS NULL Now we have a lists of each minute for which there was no data logged. It should be easier to report "not logged time spans" by using external programming logic than by constructing endless SQL. This and other solutions will not work unless the logging is supposed to be unique to each minute. For data logged approximately each minute the phase of the sampling must be adjust to suit. If the following are valid and expected time values 00:00:05 00:00:55 [apparent gap] 00:02:10 00:03:15 00:03:45 [apparent gap] 00:05:10 then the sampling point would be better placed on 30 second boundaries instead of zero second boundaries. In this case the integer division function DIV must be modified to account for the offset, as must the SELECT query. Regards, Pete _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
