将trunc_date postgres timestamp传递给php日期导致错误的日期

Question

I'm using the following SQL query in postgres:

SELECT
    date_trunc('month', s.thedate),
    r.rank,
    COUNT(r.rank)
FROM
    serps s
    LEFT JOIN ranks r ON r.serpid = s.serpid
GROUP BY
   date_trunc('month', s.thedate), s.thedate, r.rank
ORDER BY
   s.thedate ASC;

when I run that query directly against the database, I get the data all the data I need and the dates seem to be correct (formatted in Y-m-d g:i:s).

However, when I run it with PHP, Postgres instead of the date returns the timestamp.

Therefore, when I use that timestamp in PHP date, the whole date is incorrect.

For instance:

The first row Postgres displays it as:

"2013-08-01 00:00:00, 36, 1"

but PHP receives:

"1375315200000, 36, 1"

When I try to do:

echo date("Y-m-d", 1375315200000);

The output is:

45552-01-02

instead of

2013-08-01

At first I thought it was a padding issue, perhaps? I dropped the last three zeros in the timestamp so:

echo date("Y-m-d", 1375315200);

and that returns:

2013-07-31

My questions are:

1) Is it only a coincidence that after dropping three zeros, the timestamp represent a day before the actual date stored in the database?

2) Why Postgres interprets the timestamp correctly; whereas php doesn't? According to the documentation Postgres timestamp should be in the unix timestamp format.

Answer 1

The number they're returning is milliseconds in the Unix era, rather than seconds. Dividing by 1000 before feeding it to PHP is necessary.

With databases, be careful to check whether their timestamp is actually UTC/GMT, or has been offset to the server's timezone. I've seen both done. My server, located in California, is Pacific Time for MySQL timestamps. Be careful about sticking PHP timestamps into the database and then formatting with SQL, or vice-versa.