0

I'm using two timestamps, last_edited and last_uploaded, to tell if a SQLite entry needs to be uploaded to my server. However, even though I'm using datetime('now') whenever I update these timestamps, the timestamp in last_uploaded is saved as my local time (UTC +2).

Any ideas on why this is or how I can fix it?

My SQLite statements are:

private static final String UPDATE_EDITED_TIMESTAMP_BY_ID =
      "UPDATE " + TABLE_ENTRIES + "\n"
    + "SET " + COLUMN_ENTRIES_LAST_EDITED + " = DATETIME('now')\n"
    + "WHERE " + COLUMN_ENTRIES_ID + " = :id;";

private static final String UPDATE_SYNC_INFO_BY_ID =
      "UPDATE " + TABLE_WORK_ENTRIES + "\n"
    + "SET " + COLUMN_ENTRIES_LAST_SYNCED + " = DATETIME('now'), " + COLUMN_ENTRIES_SERVER_ID + " = :server_id\n"
    + "WHERE " + COLUMN_ENTRIES_ID + " = :db_id;";

When I run them, I'm using code like:

String sql = UPDATE_EDITED_TIMESTAMP_BY_ID.replace(":id", Integer.toString(id));
db.execSQL(sql);
Cornholio
  • 985
  • 1
  • 5
  • 22
  • why are you using :id only to replace it later ? there is already a mechanism in android using '?' and the `args` param of the `db.update()` method. – njzk2 Apr 16 '13 at 11:49
  • explain `the timestamp in last_uploaded is being saved as localtime.` – njzk2 Apr 16 '13 at 11:50
  • @njzk2: I use :id just for readability of the SQL - that's just how I do it. About being saved at localtime, see the edit in my post. – Cornholio Apr 16 '13 at 12:00
  • I should also clarify - I know that the `:id` replacing isn't what's wrong. – Cornholio Apr 16 '13 at 12:07
  • 1
    that may be how you do it, but that's not what is recommended. The '?' mechanism in android allows sqlite to recognize identical queries and to properly escape arguments. you really should use it. Plus, the update method returns a value that gives you an indication as to the success of your query, while execSQL doesn't. – njzk2 Apr 16 '13 at 12:24
  • Then I'll definitely consider that. However, that's not the issue, because I'm checking the values before and after execution in the sqlite shell and it's definitely updating. – Cornholio Apr 16 '13 at 12:52
  • 1
    explain `the timestamp in last_uploaded is being saved as localtime.`. How do you observe that? – njzk2 Apr 16 '13 at 13:04
  • Say the time where I am is 8:00 AM. UTC, that would be 6 AM. When this code runs, instead of showing up as 6:00 am, the timestamp says 8:00. I need the timestamps to be in the same time zone so I can compare them. – Cornholio Apr 16 '13 at 13:18
  • timestamps are not timezone relative. they are always UTC. Timezone are added at display time. – njzk2 Apr 16 '13 at 13:24
  • Regardless of what should be happening, the `last_uploaded` timestamp is 2 hours ahead of the actual time in UTC. – Cornholio Apr 16 '13 at 13:29
  • my understanding is that datetime returns a string, which is a representation, and it may not contain the timezone. I would use a plain unix timestamp, and get it using System.currentTimeMillis() from the android part – njzk2 Apr 16 '13 at 13:43
  • I'll try that, and also switch to using update() wherever I can. – Cornholio Apr 16 '13 at 13:44
  • @njzk2: I did both things you suggested, and it's working now - Want to write an answer I can accept? – Cornholio Apr 17 '13 at 08:20

0 Answers0