db_format_tsfield() returning time value with offset on Postgres 9.3

Bug #1353069 reported by Yuliya Bozhko on 2014-08-05
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Mahara
Critical
Yuliya Bozhko
1.10
Critical
Unassigned
1.8
Critical
Unassigned
1.9
Critical
Unassigned
15.04
Critical
Yuliya Bozhko

Bug Description

On postgres db_format_tsfield() calculates time by doing FLOOR(EXTRACT(EPOCH FROM {$field})) which apparently doesn't make sense if we are using fields without timezone (by reading this http://postgresql.1045698.n5.nabble.com/Possible-problem-with-EXTRACT-EPOCH-FROM-TIMESTAMP-td2125933.html).

Changed in mahara:
status: New → In Progress
assignee: nobody → Yuliya Bozhko (yuliya.bozhko)
Aaron Wells (u-aaronw) wrote :

Worth noting, there's an in-depth discussion of how Mahara handles time values here: https://mahara.org/interaction/forum/topic.php?id=6045#post25831

And, indeed, it is written with the assumption that the (no-TZ) timestamp stored in the DB will have the database server's timezone applied to it implicitly, before the "extract epoch" operation. So if that behavior has changed in Postgres 9.2 then we certainly do need to be ready for that.

Aaron Wells (u-aaronw) wrote :

To any future readers who've stumbled across this bug report because you're having time issues in Mahara, it's also worth noting that there's a separate problem that can be caused if Mahara's web server has a different timezone than its database server, and we added a $cfg->dbtimezone setting in config-defaults.php to take care of this: https://bugs.launchpad.net/mahara/+bug/1187212

Aaron Wells (u-aaronw) on 2014-11-11
Changed in mahara:
importance: Undecided → Critical
milestone: none → 15.04.0
Aaron Wells (u-aaronw) wrote :

Attaching a test script to help with troubleshooting time issues. It models the entire lifecycle of a timestamp in Mahara:

1. Epoch fetch from time()
2. Converted to a date-time string by db_format_timestamp()
3. Stored into the DB as a timestamp without timezone
4. Retrieved from the DB as an epoch
5. Converted into a human-readable string with strftime()

Aaron Wells (u-aaronw) wrote :

Attaching an improved version of the timezone troubleshooting patch.

It turns out my problem was that my Postgres database was using the timezone "GMT" for some reason, instead of "Pacific/Auckland".

Reviewed: https://reviews.mahara.org/3573
Committed: http://gitorious.org/mahara/mahara/commit/e4d1c89abca75b0fe44682c0bfd5be94badfa00d
Submitter: Aaron Wells (<email address hidden>)
Branch: master

commit e4d1c89abca75b0fe44682c0bfd5be94badfa00d
Author: Yuliya Bozhko <email address hidden>
Date: Wed Aug 6 07:47:36 2014 +1200

Fix db_format_tsfield() returning value with offset on Postgres 9.2 and later (Bug #1353069)

Change-Id: I827851a16eba74d474234c21dd6b6afc2d06470c
Signed-off-by: Yuliya Bozhko <email address hidden>

Mahara Bot (dev-mahara) wrote :

Patch for "1.8_STABLE" branch: https://reviews.mahara.org/3974

Reviewed: https://reviews.mahara.org/3972
Committed: http://gitorious.org/mahara/mahara/commit/cca5921ca6433d3c49f8ab17286d4bb8747b9a1e
Submitter: Aaron Wells (<email address hidden>)
Branch: 1.10_STABLE

commit cca5921ca6433d3c49f8ab17286d4bb8747b9a1e
Author: Yuliya Bozhko <email address hidden>
Date: Wed Aug 6 07:47:36 2014 +1200

Fix db_format_tsfield() returning value with offset on Postgres 9.2 and later (Bug #1353069)

Change-Id: I827851a16eba74d474234c21dd6b6afc2d06470c
Signed-off-by: Yuliya Bozhko <email address hidden>

Mahara Bot (dev-mahara) wrote :

Reviewed: https://reviews.mahara.org/3973
Committed: http://gitorious.org/mahara/mahara/commit/aa0105e6f5c1a02930e5d79a60ddc88ce5934af0
Submitter: Aaron Wells (<email address hidden>)
Branch: 1.9_STABLE

commit aa0105e6f5c1a02930e5d79a60ddc88ce5934af0
Author: Yuliya Bozhko <email address hidden>
Date: Wed Aug 6 07:47:36 2014 +1200

Fix db_format_tsfield() returning value with offset on Postgres 9.2 and later (Bug #1353069)

Change-Id: I827851a16eba74d474234c21dd6b6afc2d06470c
Signed-off-by: Yuliya Bozhko <email address hidden>

Mahara Bot (dev-mahara) wrote :

Reviewed: https://reviews.mahara.org/3974
Committed: http://gitorious.org/mahara/mahara/commit/6e0d6e9404f4067693bd0cd6481930598300dedf
Submitter: Aaron Wells (<email address hidden>)
Branch: 1.8_STABLE

commit 6e0d6e9404f4067693bd0cd6481930598300dedf
Author: Yuliya Bozhko <email address hidden>
Date: Wed Aug 6 07:47:36 2014 +1200

Fix db_format_tsfield() returning value with offset on Postgres 9.2 and later (Bug #1353069)

Change-Id: I827851a16eba74d474234c21dd6b6afc2d06470c
Signed-off-by: Yuliya Bozhko <email address hidden>

Aaron Wells (u-aaronw) on 2014-11-17
summary: - db_format_tsfield() returning time value with offset on POstgres
+ db_format_tsfield() returning time value with offset on Postgres 9.3
Robert Lyon (robertl-9) on 2015-04-17
Changed in mahara:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers