db_format_tsfield() returning time value with offset on Postgres 9.3

[Yuliya Bozhko]

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

[Mahara Bot (dev-mahara)]

Patch for "master" branch: https://reviews.mahara.org/3573

[Simon Coggins (simon-coggins)]

More info here:

http://dba.stackexchange.com/questions/29960/why-how-did-the-behavior-of-extractepoch-from-timestamp-without-time-zone

This bug only affects Postgres 9.2+ due to a compatibility change in 9.2.

[Aaron Wells (u-aaronw)]

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

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

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

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

[Mahara Bot (dev-mahara)]

Patch for "1.10_STABLE" branch: https://reviews.mahara.org/3972

[Mahara Bot (dev-mahara)]

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

Patch for "1.9_STABLE" branch: https://reviews.mahara.org/3973

[Mahara Bot (dev-mahara)]

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

[Mahara Bot (dev-mahara)]

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

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

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>