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 <kmedc...@dessus.com> 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 " 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