6 Replies - 8249 Views - Last Post: 04 September 2010 - 01:13 PM Rate Topic: -----

#1 sg552  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 16
  • Joined: 16-November 09

Convert MySQL DATETIME to local time

Posted 04 September 2010 - 10:50 AM

Hello everyone.

I need help converting my database DATETIME to local time. My local time is Asia/Kuala Lumpur GMT+8

This is what I have in my database:
2010-09-04 15:39:44


My Insert syntax
$query = " INSERT INTO article (title, date_time, content) ".
            " VALUES ('$title', NOW(), '$content')";


This code,
echo date('d-m-Y, g:i a',strtotime($article_date));


will output this:
04-09-2010, 3:39 pm


the date is working fine but the time is not. I insert this into database at 11.39 PM so the output I expected is:
04-09-2010, 11:39 pm


Also I'm testing this on localhost so I think there suppose to be no offset problem (I think).

I'm open to all suggestion. Any help is appreciated. Thanks for your time :)

UPDATE: I look into my php.ini directive and change date.timezone = "UTC" to GMT but the result still the same :(

Is This A Good Question/Topic? 0
  • +

Replies To: Convert MySQL DATETIME to local time

#2 Dormilich  Icon User is online

  • 痛覚残留
  • member icon

Reputation: 3569
  • View blog
  • Posts: 10,402
  • Joined: 08-June 10

Re: Convert MySQL DATETIME to local time

Posted 04 September 2010 - 11:11 AM

as your timezone is GMT +08:00 I’d expect no changes (UTC = GMT), you would have to set "Asia/Kuala_Lumpur" as timezone.

This post has been edited by Dormilich: 04 September 2010 - 11:15 AM

Was This Post Helpful? 0
  • +
  • -

#3 sg552  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 16
  • Joined: 16-November 09

Re: Convert MySQL DATETIME to local time

Posted 04 September 2010 - 11:45 AM

View PostDormilich, on 04 September 2010 - 10:11 AM, said:

as your timezone is GMT +08:00 Id expect no changes (UTC = GMT), you would have to set "Asia/Kuala_Lumpur" as timezone.


ah yes! Actually I change it before this to "Asia/Kuala Lumpur" but I forgot the underscore "_" and PHP just spit out error. silly me. Refer to >> http://www.php.net/m...ezones.asia.php

Anyway
echo date('d-m-Y, g:i A');
will output this
05-09-2010, 2:35 AM
which is my current time and works like it should, (it's morning now in Malaysia) but the output I'm getting from database is still
04-09-2010, 3:39 pm
even when I insert query at 11:39 pm. Like I'm pointing above^^

Dormilich thanks for your input :) I hope anybody can give me more solution. Thanks. I'm really sleepy now...
Was This Post Helpful? 0
  • +
  • -

#4 Dormilich  Icon User is online

  • 痛覚残留
  • member icon

Reputation: 3569
  • View blog
  • Posts: 10,402
  • Joined: 08-June 10

Re: Convert MySQL DATETIME to local time

Posted 04 September 2010 - 11:47 AM

you could try setlocale() / strftime()
Was This Post Helpful? 0
  • +
  • -

#5 sg552  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 16
  • Joined: 16-November 09

Re: Convert MySQL DATETIME to local time

Posted 04 September 2010 - 12:37 PM

View PostDormilich, on 04 September 2010 - 10:47 AM, said:

you could try setlocale() / strftime()



OK after changing php.ini setting date.timezone = "Asia/Kuala_Lumpur" isn't the NOW() query will store DATETIME to local time. I mean isn't this query:

$query = " INSERT INTO article (date_time) ".
            " VALUES (NOW())";


will store DATETIME according to server time. For example if server time is 05 September 2010 , 3.36 am isn't the date_time field will have this value
2010-09-05 03:36:44
?

no?
Was This Post Helpful? 0
  • +
  • -

#6 Dormilich  Icon User is online

  • 痛覚残留
  • member icon

Reputation: 3569
  • View blog
  • Posts: 10,402
  • Joined: 08-June 10

Re: Convert MySQL DATETIME to local time

Posted 04 September 2010 - 12:39 PM

ok, anything you set in PHP will have no effect on the MySQL command NOW()
Was This Post Helpful? 1
  • +
  • -

#7 sg552  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 16
  • Joined: 16-November 09

Re: Convert MySQL DATETIME to local time

Posted 04 September 2010 - 01:13 PM

View PostDormilich, on 04 September 2010 - 11:39 AM, said:

ok, anything you set in PHP will have no effect on the MySQL command NOW()


My whole understanding is wrong from the start. I thought php.ini can change the data inserted to the database. That's why I thought no offset with my localhost.

Anyway I have the solution now. Credits to Soylent and all commenter @ http://www.askdaveta...e_function.html

$timezone_offset = +8; // us central time (gmt-6) for me
$article_date = date('d-m-Y, g:i A', strtotime($article_date)+$timezone_offset*60*60);


Thanks to Dormilich for your quick reply. :) Thanks everyone.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1