How can i change the timestamp to my own timezone in mysql?

Question

Here is my PHP code below:

$sql = "CREATE TABLE `mytable` (
`id` int(6) unsigned not null auto_increment,
`firstname` varchar(30) not null,
`lastname` varchar(30) not null,
`email` varchar(50),
`reg_date` timestamp not null default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ";

is that what you want? - http://stackoverflow.com/questions/930900/how-to-set-time-zone-of-mysql — Guilherme Ferreira, Apr 22 '15 at 15:30
You will need to edit your conf file if your server is in a different zone. — Kisaragi, Apr 22 '15 at 15:32
@GuilhermeFerreira: I am a newbie... and I just saw the link you gave me but I don't know where to put those codes.... can you please help me where should I put them? THanks... — Goldy, Apr 22 '15 at 15:36
Okey, first find your "my.cnf" or "my.ini" file (follow that link http://stackoverflow.com/questions/2482234/how-to-know-mysql-my-cnf-location). If doesn't exists, please create. And then, you can set up it with that line inside your file -> default_time_zone='+00:00' — Guilherme Ferreira, Apr 22 '15 at 15:40
@GuilhermeFerreira, I am only using WAMP, and I believe I don't have those options... — Goldy, Apr 22 '15 at 15:50
Are you using some MySQL client ? MySQL Workbench / HeidiSQL ? — Guilherme Ferreira, Apr 22 '15 at 15:53
@GuilhermeFerreira I am using WAMP and the version of MySQL is 5.5.28....and all I can see is SQL Buddy..... — Goldy, Apr 22 '15 at 15:56
Try it BEFORE your 'CREATE' --> SET GLOBAL time_zone = 'your_timezone'; — Guilherme Ferreira, Apr 22 '15 at 16:01

score 0 · Answer 1 · edited May 23 '17 at 11:51

For a single table and column, you could make a view, converting only the timestamp column from the server's timezone to your actual timezone when you select from the view instead of the table itself.

CREATE VIEW `v_mytable` AS
select id
      ,firstname
      ,lastname
      ,email
      ,CONVERT_TZ(regdate,'UCT','US/Pacific') as regdate
 from mytable;

If you don't have CONVERT_TZ installed it's outlined over here how to install it: MySQL CONVERT_TZ()

How can i change the timestamp to my own timezone in mysql?

1 Answers1