3 Replies Latest reply on Jan 13, 2010 12:20 PM by coshima009

    AIR SQLite timezone question (JavaScript)


      SQLite stores dates in UTC, which is great, but when I fetch the value, the resulting date object is incorrectly offset. Hopefully I'm missing something obvious and someone can point it out. Here's what I'm doing:


      CREATE TABLE myTable ("id" INTEGER PRIMARY KEY ,"created" DATE);

      INSERT INTO myTable (id,created) VALUES (1,DATETIME('NOW'));

      SELECT id,created FROM myTable WHERE id=1;


      My expectation is that the INSERT will store the current date/time adjusted to UTC. Because the AIR runtime casts date values to JavaScript date objects, my expectation is that the SELECT would give me back a valid date object, from which I could display either UTC or local time information. And it almost does.


      Suppose 'NOW' is 11:42:23 AM Pacific Time (GMT-8). I would expect the time information from toString() on the resulting date object to print something like "11:42:23 GMT-0800" but instead, I'm getting "19:42:23 GMT-0800". Clearly, it's a timezone issue because minutes and seconds look good. It's like AIR (or SQLite) is assuming that the value "11:42:23" is already UTC when inserting, and when AIR creates the resulting date object, it's applying the 8 hour timezone offset.


      I tried the solution posted here, which is to modify the INSERT as follows.


      INSERT INTO myTable (id,created) VALUES (1,DATETIME('NOW','localtime'));


      That works, but I don't want to store these values as local times, I want to store them as UTC and display them as local times. Meaning I should be able to store "11:42:23" in the Pacific timezone, and if I change to Eastern, I expect that value to be displayed as "14:42:43". When I use this solution, toString prints "11:42:23 GMT-0500".

        • 1. Re: AIR SQLite timezone question (JavaScript)
          coshima009 Level 1

          Jumped the gun there and didn't finish my post.


          I was going to observe that this result is clearly not desired (just calling "11:42" an Eastern time).


          This has to be such a common issue, but I haven't been able to find a solution. Well, I found this, but it's for ColdFusion, and I really think I must just be missing something, because there must be an easier way to do this. Thanks for any thoughts!

          • 2. Re: AIR SQLite timezone question (JavaScript)
            coshima009 Level 1

            Update: Based on this topic on Stack Overflow, I tried changing the value in the insert from "DATETIME('NOW')" with a parameter:


            stmt.parameters[":created"] = new window.runtime.Date();

            (Aside: it's kind of annoying that AIR does not support JS dates, I'd like to see that change.)


            Anyway, this still doesn't solve the original problem. It works just like using DATETIME('NOW','localtime'). If the value goes in at 1:54pm PST, and I change the timezone to EST and read the value, it displays as "1:54pm EST", when I would expect it to be "4:54pm EST".


            Any advice is appreciated. Thanks!


            • 3. Re: AIR SQLite timezone question (JavaScript)
              coshima009 Level 1

              Well, it doesn't look like an answer is coming anytime soon. For the record, in case anyone has the same question, my workaround (and it seems others have gone this route as well) was to change my DATE columns to INT, and just record the timestamp:


              stmt.parameters[":created"] = new Date().getTime();

              Now the value is timezone neutral, and I can display the proper value adjusted for the local timezone. It feels dirty to do it this way, but it works perfectly.