Page MenuHomePhabricator

postgres timestamps showing as 01/01/1970
Closed, ResolvedPublic


Author: to_allo

I run mediawiki 1.8.0 (with Greek language interface) coupled with Postgres
8.1.4 using PHP 5.1.4-0.1. The same bug was present with 1.7.1 as well.

Almost everywhere where dates and times are to displayed, I get "00:00, 1
January 1970". There are some exceptions, which I will proceed to list at the
end of this report. This time defaults to 0 Unix ticks, so I assume that
something is not parsed properly.

For example, all page-last-modified footers, user contributions, history pages
and diffs render the timestamp as such. However by inspection of the database
tables, I have verified that the information is indeed in there, for example
rev_timestamp fields in the revision table read "2006-10-20 03:14:52+03",
"2006-10-20 03:08:55+03" etc. I use phppgadmin to inspect the database.

Regarding the locale of the Postgres database, the environment variables that
may be
changed from within postgresql.conf (taking effect upon each server restart) are:
lc_messages = 'C' # locale for system error messages
lc_monetary = 'el_GR' # locale for monetary formatting
lc_numeric = 'el_GR' # locale for number formatting
lc_time = 'el_GR' # locale for time formatting

However, I tried with lc_time = 'C' as well and got the same behavior.
As for lc_collate, it is determined upon Postgres installation and may not change;
it is 'el_GR' as well.

Exceptions to dates and times not showing up properly:

  • The "Recent changes" special page correctly renders the start of the displayed

recent changes (i.e. 01:16, 20 Οκτωβρίου 2006); however, all listed entries are
lumped together under 01 January 1970 once again.

Version: 1.8.x
Severity: normal
OS: Linux
Platform: PC



Related Objects

View Standalone Graph
This task is connected to more than 200 other tasks. Only direct parents and subtasks are shown here. Use View Standalone Graph to show more of the graph.

Event Timeline

bzimport raised the priority of this task from to Medium.Nov 21 2014, 9:23 PM
bzimport set Reference to bz7637.
bzimport added a subscriber: Unknown Object (MLST).

to_allo wrote:

I should add that in the "Recent Changes" special page, as well as in "User
Contributions" edits are actually listed in the correct order. That is, most
recent edits do appear on top. Nevertheless the respective dates adn times are
invariably shown as 00:00, 01 January 1970.

This demonstrates that at the database level everything is alright (queries with
"order by" work properly). It is in the rendering of the timestamps by mediawiki
that the problem lies.

mathias.behrle wrote:

I am working on a fresh install of Mediawiki SVN 1.10alpha (r20060) on
PostgreSQL and so far see all dates in correct format. Is this bug already fixed?

Closing as fixed, please reopen if behavior is still seen in recent subversion

to_allo wrote:

I only recently found out what was causing the problematic behavior and would thus like to list it here for other people:

Mediawiki expects the following setting for Postgres (in postgresql.conf):

datestyle = 'ISO'

As long as this was set to

datestyle = 'sql, european' //original setting

I got the above described behavior. Upon modifying it, timestamps render alright.

What LC_TIME is set to (in postgresql.conf as well) is irrelevant and does not affect the behavior (up to 1.10 at least).

I am only reopening this because, in principle, shouldn't the SQL datestyle be supported as well? I did not find in any documentation the requirement that the datestyle be ISO for Mediawiki to cope; changing a database-wide setting just for Mediawiki is not always practical/feasible either.

By the way, Greg, thumbs up to you man! Just like you said elsewhere, best Postgres support ever!

IIRC the installer/updater was recently changed to ensure that the DateStyle was set this way.

Closing as fixed, datestyle is indeed now enforced on install/update.

Jdforrester-WMF subscribed.

Migrating from the old tracking task to a tag for PostgreSQL-related tasks.