PHP Datetime with MySQL datetime

Question

I want to ask about changing a datetime value of PHP with datetime value from MySQL data.

I have try to do this at PHP:

$sitgl = date('Y-m-d', strtotime(2012-01-12));
$sijam = date('H:i:s', strtotime(13:00:00));
$awal = $sitgl.' '.$sijam;
$awal2 = date('Y-m-d H:i:s', strtotime($awal));
$debrangkat = strtotime($awal2);

And I'm trying to convert same datetime at MySQL like this (convert it to seconds):

SELECT date_start_book, time_start_book, (TO_DAYS(CAST(date_start_book AS DATE))*86400) + TIME_TO_SEC(CAST(time_start_book AS TIME)) FROM `t_request_queue` WHERE `request_id` = '1301-0087'

which is date_start_book value is 2012-01-12 and time_start_book value is 13:00:00

My question is: why the PHP code return value : 1357970400 but the MySQL value return 63525214800 ?

what must I do to make both of value is same? Is strtotime() not return a seconds or why?

actually I'm getting $sitgl from a row from database which is [date_start_book], I don't want to confusing my question so I'm changing them to the value of that rows... — Celia Tan
– Celia Tan, Commented Jan 12, 2013 at 3:24

Hanky Panky · Accepted Answer · 2013-01-12 04:42:11Z

First of all as others have suggested that php code is really hurting brain. You could make that Unix Timestamp in just one line. But to answer your real question. MYSQL TO_DAYS works different than PHP UNIX Timestamp

According to MySQL Website

Given a date date, returns a day number (the number of days since year 0). 
mysql> SELECT TO_DAYS(950501);
        -> 728779
mysql> SELECT TO_DAYS('2007-10-07');
        -> 733321

 TO_DAYS() is not intended for use with values that precede the advent of the Gregorian calendar (1582), because it does not take into account the days that were lost when the calendar was changed. For dates before 1582 (and possibly a later year in other locales), results from this function are not reliable

And according to PHP Website timestamp is

Returns the current time measured in the number of seconds since the Unix Epoch (January 1 1970 00:00:00 GMT).

And hence the difference in two values. Their starting point is way too distant from each other. MySQL starts from year 0 and PHP starts from year 1970.

Suggestion

I would suggest you save php's timestamp in mysql rather than a formatted date time. This will help you stay consistent and allow you to perform any date or time comparisons easily.

Celia Tan · Accepted Answer · 2013-01-14 02:26:43Z

0

Finally, I change the PHP to datetime and at query I'm using ADD_DAYS to add a date with a seconds then I compare it with the PHP datetime result.

So many thanks to all contributor.

answered Jan 14, 2013 at 2:26

Celia Tan

1657 bronze badges

Collectives™ on Stack Overflow

PHP Datetime with MySQL datetime

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related