Re: [sqlite] Timestamps from different timezones
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 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: 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 " -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
Re: [sqlite] Timestamps from different timezones
>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 " -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
Re: [sqlite] Timestamps from different timezones
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.1300: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 " -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
