← Back to team overview

desktop-packages team mailing list archive

[Bug 854344] Re: Gwibber 3.0.0.1 incorrectly storing timestamps in SQLite database

 

The time stored in the database is the time given to us from the social
network for the account (facebook,twitter, etc).  So gwibber doesn't
really do any real magic with the time to convert it, gwibber simply
stores the time the post was made or last updated on the web service.
Doing it this way helps make results consistent with what you would see
in the web browser, however it does make you need to jump through some
hoops if you need to do any calculations in gwibber.  We do some things
in the service plugins to calculate an offset time to determine what new
data to fetch from the API, something like this in python:

since = int(mx.DateTime.DateTimeFromTicks(mx.DateTime.localtime()) -
mx.DateTime.TimeDelta(hours=240.0))

-- 
You received this bug notification because you are a member of Desktop
Packages, which is subscribed to gwibber in Ubuntu.
https://bugs.launchpad.net/bugs/854344

Title:
  Gwibber 3.0.0.1 incorrectly storing timestamps in SQLite database

Status in Gwibber:
  New
Status in “gwibber” package in Ubuntu:
  New

Bug description:
  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.

To manage notifications about this bug go to:
https://bugs.launchpad.net/gwibber/+bug/854344/+subscriptions


References