Hello Christopher,
> You absolutely cannot do this automatically unless you know > the source of > each timestamp, any maybe not even then. > Just as a f'rinstance, there are at least 3 different meanings > for EST, and > 2 of them are in the same country: > try -0500, +1000, +1100 for a start. Thanks for your reply. I was afraid that this would be the answer... Thanks again bernie >-----Original Message----- >From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- >boun...@sqlite.org] On Behalf Of Bernie Reiter >Sent: Saturday, 23 November, 2013 16:54 >To: sqlite-users@sqlite.org >Subject: [sqlite] Timestamps from different timezones > >Dear List, > >I receive data records from various sources. I can't influence their >formats. These data records are containing datetimestamps like this >(without the doublequotes): > >"06-May-12 18:57:41 BST" >"Nov-22-13 22:58:10 PST" >"23-Nov-13 08:56:57 GMT" >"22.11.13 00:33:32 MEZ" >"23-Nov-13 18:57:40 AEDST" >"23-Nov-13 01:58:10 EST" > >I want to automatically convert these datetimestamps into standard UTC. > >Currently I am using a "manual approach" by: >a) converting the datetimestamp into the common format " YYYY-MM- >DDTHH:MM" >b) reading the time zone code (e.g., BST) >c) looking-up the timezone indicator for this time zone code (e.g. - >01:00) >(see paragraph "Formats 2 through 10 may be optionally followed by a >timezone indicator..." >on page http://sqlite.org/lang_datefunc.html ) >d) and appending this indicator to an SQL statement. > >My SQL statements look like this: >SELECT datetime('2012-05-06T18:57:41-01:00'); > >Is there a clever way to convert these datetimestamps into standard UTC ? > > >I don't want to "mess around" with the local timezone of the server >on which sqlite3 is running. This approach is not portable. > >The Unix/Linux 'date' utility is not portable either. > >Thanks a lot > >bernie >_______________________________________________ >sqlite-users mailing list >sqlite-users@sqlite.org >http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
