Public bug reported: While researching to build a script that would automatically purge messages older than X amount of time from the SQLite database, I found that the "from the epoch" timestamps being stored in messages.time aren't really "from the epoch".
As per all definitions, "the epoch" starts at "1/1/1970 00:00:00.000 GMT" (the key point being GMT). Gwibber is storing the "correct" timestamp, but not taking the active timezone into account. That is to say, it's storing the "epoch" number into the database as if the local time were in GMT regardless of the truth. Assume a configured system timezone of CST. The current query returns the correct values for the message dates: select time, datetime(time, 'unixepoch') from messages order by time asc; However, clearly this is incorrect as the system is NOT in the GMT timezone. The timestamps stored should be relative GMT. This next query should be the correct one, in order to account for the timezone difference. However, the results are (predictibly) off by 6 hours (in the case of CST, which is the timezone delta): select time, datetime(time, 'unixepoch', 'localtime') from messages order by time asc; The 'localtime' argument instructs SQLite to take that timezone delta into account. (as per instructions from http://www.sqlite.org/lang_datefunc.html) The problem is that now, in order to create my purge script, I have to take into account the timezone that I'm running in, as opposed to simply saying "delete the messages older than X hours". While this might appear to be a minor inconvenience, this defect makes it impossible to share the script as a black box solution for other users, since now the script would have to be configured for each user individually, to account for each of their own timezones. Not to mention to account for timezone shifts. ** Affects: gwibber Importance: Undecided Status: New ** Affects: gwibber (Ubuntu) Importance: Undecided Status: New ** Also affects: gwibber (Ubuntu) Importance: Undecided Status: New -- You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. https://bugs.launchpad.net/bugs/854344 Title: Gwibber 3.0.0.1 incorrectly storing timestamps in SQLite database To manage notifications about this bug go to: https://bugs.launchpad.net/gwibber/+bug/854344/+subscriptions -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
