Date/Time differences between MySql and PHP?

Question

I have this table

Name                       Birth_Date            Register_Date
---------------------------------------------------------------
Ali                       1990-03-22             2010-03-1 15:1:42
Ali1                      1991-07-18             2010-03-2 12:44:2

When I inserted these values, I inserted the Birth_Date as a String such as '1990-03-22', and I used 'NOW()' for Register_Date.

NOW() will generate the current datetime according to the MySql Server.

Now when I try to get the time between the current date and the Register_Date (Time passed since he registered), I use the following:

SELECT YEAR(CURDATE())-YEAR(register_date) ...

In PHP, if I wanted to do that, I suppose I have to get the current date: date('Y-m-d H:i:s');

My question is, is there a difference between calculating the date difference (between a date and today) via MySql or via PHP?

Currently, on my localhost (XAMPP), CURDATE() and date(..) generates the same date, but will it generate the same date for other users when my website goes online?

SELECT YEAR(CURDATE())-YEAR(register_date) ... Will return nothing because CURDATE() year is same as YEAR(register_date) — Vishwanath Dalvi, Mar 29 '13 at 16:48
@VishwanathDalvi Ignore 2013 I was only providing an example, I changed it to 2010. — Ali Bassam, Mar 29 '13 at 16:49

score 1 · Accepted Answer · answered Mar 29 '13 at 16:48

1

If both your mysql and PHP server are operating on the same timezone and have their clocks properly synchronized, you wont have an issue.

answered Mar 29 '13 at 16:48

Ian

24,116
22
58
96

By default are they operating on the same timezone? and how do I make sure of this? – Ali Bassam Mar 29 '13 at 16:51
There is no default timezone. This is a server configuration. `mysql> SELECT @@global.time_zone, @@session.time_zone;` will let you check the mysql timezone, `date_default_timezone_get()` in php will let you get the timezone. – Ian Mar 29 '13 at 16:53
I got `Europe/Berlin` in PHP (I'm not in europe). – Ali Bassam Mar 29 '13 at 17:02
You're going to want to fix that then. ;) SYSTEM in mysql means its using the timezone of the OS. – Ian Mar 29 '13 at 17:59

score 0 · Answer 2 · answered Mar 29 '13 at 16:57

if you want to upload your php website and your DB on the same server I think you'll not have problem , but If you use different servers you may have time issues.

to avoid this issue I advice you to save the time with time zone, to be able to get the correct time from any server.

Date/Time differences between MySql and PHP?

2 Answers2

Linked