On 1/6/15, MikeSnow <[email protected]> wrote:
> As I look through the posts, i dont see what I am looking for.....
>
> I am trying to create a CASE statement that converts time zones to UTC for
> db storage.
> For example, I have 3 columns, ReceiveDate, Timezone, UTC Datetime.
> 04/11/2014 2:00:00, EST, <empty>
You have a database. So use it. Create a "timezone" table that maps
the timezone name into an offset from UTC. Ex:
CREATE TABLE timezone(tz TEXT PRIMARY KEY, ofst TEXT) WITHOUT ROWID;
INSERT INTO timezone(tz,ofst) VALUES('EST','-05:00'),('EDT','-06:00'), ...;
Then if you have inputs $ReceivedDate and $Timezone, store as follows:
datetime($ReceivedDate || (SELECT ofst FROM timezone WHERE tz=$Timezone));
The || operator is concatenate. So if $ReceivedDate is 2015-01-07
12:00:00 and $Timezone is "EST" then the concatenation will be
"2015-01-07 12:00:00-0500" which the datetime() function converts to
UTC: "2015-01-07 17:00:00".
Note that the above requires that you have a unique set of timezone
names. In other words, every timezone name has a unique offset from
UTC. Good luck with that!
>
> I would like to update UTC Datetime with logic based off "Timezone" col to
> store as UTC?
>
> Something like if
> Update t1. SET "UTC Datetime"=if "TimeZone"='EST', then "ReceiveDate"+5
> 04/11/2014 2:00:00, EST, 04/11/2014 7:00:00
> I would imagine someone has already gone down this path?
> thanks in advance
> Mike
>
>
>
> --
> View this message in context:
> http://sqlite.1065341.n5.nabble.com/Time-Zone-Conversions-tp79849.html
> Sent from the SQLite mailing list archive at Nabble.com.
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
D. Richard Hipp
[email protected]
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
