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.
On 24 November 2013 10:53, Bernie Reiter < bernie_on_the_road_ag...@yahoo.co.uk> wrote: > 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 > -- Christopher Vance _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
