Some tables use the "date" datatype. In the old system (psql 9.1/PHP5.3), when the date is stored in the database, it is stored correctly. However, in the new system (psql 9.6/PHP 7.1), the date stored incorrectly. For example, when storing 7/13/10 in the old system, it stores the following:
stage_date | to_timestamp
------------+------------------------
1279004400 | 2010-07-13 00:00:00-07
When storing the same date in the new system, it stores the following:
stage_date | to_timestamp
------------+------------------------
1278979200 | 2010-07-12 17:00:00-07
Both Operating systems are set to "America/Los_Angeles". The old database is set to use "localtime" (which in 9.1 would use system timezone). The new database uses "PDT".
What would cause the value in the old system to be set to "1279004400" vs "1278979200" in the new system?
Part of the table definition:
Column | Type
--------------+---------------------
id | integer
stage_date | date
In case anyone else ends up here: The table view was using the extract function to convert the date to an epoch. The behavior of extract changed in PostgreSQL version 9.2.
This change reverts an ill-considered change made in release 7.3. Measuring from UTC midnight was inconsistent because it made the result dependent on the timezone setting, which computations for timestamp without time zone should not be. The previous behavior remains available by casting the input value to timestamp with time zone.
Please see the answer to the following question for more details: Different results for extract epoch on different PostgreSQL servers