Hello Keith,
Thanks for your reply.
> 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');
Yes, I do want timestamps in UTC only. Otherwise the same SELECT would return a
different value when run on a server located in the UK compared to one located
in the USA. => Not portable.
> 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?
No, because what is principally the difference when I store a datetime value in
a SQLite field whether it is expressed as UTC time or unixepoch time based on
UTC?
My application still would need to remember that this field does contain a
datetime value based on UTC...
Thanks again
bernie
On Sunday, 24 November 2013, 5:51, Keith Medcalf <[email protected]> wrote:
>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: [email protected] [mailto:sqlite-users-
>[email protected]] On Behalf Of Bernie Reiter
>Sent: Saturday, 23 November, 2013 16:54
>To: [email protected]
>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
>[email protected]
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
