[SOLVED] how to change the time zone in the DATE_FORMAT function?????????????

[cluce]

In the following string I have a system date and time that displays on a web page from the database. The problem I have is the web server is in a different time zone as I am.  Can someone tell me how to format this query to central time? Can this be done in MySQL? OR it has to be done in PHP?

 

  //gather the topics

  $get_topics_sql = "SELECT topic_id, topic_title, DATE_FORMAT(topic_create_time,  '%b %e %Y at %r') aS fmt_topic_create_time, topic_owner FROM forum_topics_reagan  WHERE topic_id = '".$_GET["topic_id"]."' LIMIT 1";

 

I know all of though operators but I don’t see anything regarding time zones.

 

if I have the server in a pacific time zone and I need the web to display central time, can someone tell me how to get this configured to display central time. I thought it would be pulling the time from the system time but I tried that and it didn’t work.

 

Is there something in myssql config/settings to change this to central? 

 

 

[cluce]

ok, I figured it is pulling the system time from the server into the mySQL? Can someone tell me how I can change this from the client side?

 

I triied to set  GLOBAL time_zone = timezone; buut I dont have super privileges.

 

and I tried this ..

 

SET time_zone = 'US/Central';

 

and it says incorrect time zone......can some one tell me if  Idid this right?

 

[fenway]

You have to query the available time zones... check the refman.

[cluce]

yes, I did check the ref and I did find the instructions on how to do it but I cant do it on my end. the web host co. has to do it. He saying it may not be possible but  Idont beleieve. b/c I knnow it can be done according to

 

http://dev.mysql.com/doc/refman/5.0/en/time-zone-support.html

 

if his giving me trouble I probably need to find out how to do it in php

 

[fenway]

You can set per-connection values.

[cluce]

thanks. for the feedback. 

 

I found 2 WAYS IN PHP HOW TO DO IT.

 

one way is

putenv("TZ=US/Eastern");

 

and the other is to add 2 hours to the system time before I display it.

[cluce]

I have decided to use mysql to change my time zone and it was easy.......

 

DATE_ADD(NOW(), INTERVAL 2 HOUR)