desktop-packages team mailing list archive

[DiegoRivera <854344@xxxxxxxxxxxxxxxxxx>]

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 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