mysql - How can i insert real time of an event into database using php mysqli? -

i'm trying add datetime check record changes. i'm using datetime datatype in table.

`date_added` datetime default '0000-00-00 00:00:00', 

i use following php built-in function using datetime column in query

date("y-m-d h:i:s"); 

problem function date("y-m-d h:i:s"); giving me 2 different date , time when check in same time on server.

localhost result

 date("y-m-d h:i:s"); == 2016-07-12 13:10:04 

server result

 date("y-m-d h:i:s"); == 2016-07-12 05:08:07 

so when use timeago function on date_added column giving me wrong time, mean server time. example add record function return me record added 8 hours ago totally wrong. know how can add real time of event database can show using timeago() function.

is there way without change server timezone, because if change timezone showing correct time in same region others? think face same issue.

i wanted develop facebook datetime functionality.

can 1 guide me how can achieve kind functionality? appreciate. thank you

instead of fiddling timezones, why not do

alter table `your_table`   change `date_added` `date_added`   timestamp not null default current_timestamp 

this change column date column timestamp column, converting dates respective utc timestamps in process.

when new row inserted, it use current timestamp value. timestamps in utc, don't have change timezone on mysql server, nor supply date when inserting new row.

if cannot or want not change columns, can select timestamp via

select unix_timestamp('date_added') your_table; 

for timeago, can do

$now = new datetime; $dateadded = new datetime("@$yourtimestampfromdb"); $dateadded->settimezone($now->gettimezone()); $timesinceadded = $dateadded->diff($now); 

when supply timestamp datetime, use utc regardless of default server timezone set. consequently, have either convert $dateadded default timezone (as shown above) or convert $timesinceadded utc.

to change datetime visiting user's timezone, either

• need have information in database, e.g. because asking registered users supply information
• or you determine @ runtime, doing geoip lookup on visiting user's ip or sending datetime offset user's browser.

in case, change both datetimes timezone. this done via settimezone().

the $timesinceadded dateinterval object, which can use this

echo $timesinceadded->format('%a total days'); 

please refer links further details, instance on available format modifiers.