>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 ?

The select returns a timestamp in UTC, not in localtime.  If you want localtime 
you need to specify that:

SELECT datetime('2012-05-06T18:57:41-01:00', 'localtime');

or do you mean something like

SELECT strftime('%s', '2012-05-06T18:57:41-01:00');

which returns the number of seconds since the unixepoch?

>-----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

Reply via email to