php date() vs mysql MONTH()

Question

Hi, i have this unix timestamp value: 1275364800 (1st june 2010).

When printing that value with php: echo date('m',1275364800) it returns 6 (thats ok)

But when i select a field from a database: select MONTH(FROM_UNIXTIME(1275364800)) AS month it returns 5

Why?

BTW, if i run this query select FROM_UNIXTIME(1275364800) AS q i get 2010-05-31 23:00:00

Answer 1

Because 1275364800 is 2010-05-31 not 1st june 2010 in mysql's locale. Are these being run on different machines?

Answer 2

To set locales in MySql do the following.

First check what your local is:

mysql> SELECT @@lc_time_names;
+-----------------+
| @@lc_time_names |
+-----------------+
| en_US           |
+-----------------+

To make sure its using the correct, If this is corrent the change your apache / php locale.

To change you locale in MySql

mysql> SET lc_time_names = 'en_UK';    
Query OK, 0 rows affected (0.00 sec)

en_UK being what you wish to change it to!

mysql> SELECT @@lc_time_names;
+-----------------+
| @@lc_time_names |
+-----------------+
| en_UK           |
+-----------------+ 

Hope this helps you!.