timestamp to use specific timezone?

Question

The below is my table definition that automatically inserts timestamp value into each record. All i want to do is let the timestamp use a specific timezone for example in my case i want it to use the current time of British Columbia How do i do that ?

Because when i insert data in the table i do not really pass a timestame value from my php script but it takes the default value.

CREATE TABLE `cfv_postbusupdate` (
  `BusNumber` int(11) NOT NULL,
  `Direction` varchar(100) DEFAULT 'Not Provided',
  `StopNames` varchar(300) DEFAULT 'Not Provided',
  `Status` varchar(45) DEFAULT 'Not Provided',
  `comments` varchar(150) DEFAULT 'None',
  `username` varchar(45) DEFAULT 'anonymous_user',
  `dayofweek` varchar(45) DEFAULT NULL,
  `time` varchar(20) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `DatePosted` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

score 0 · Answer 1 · answered Mar 28 '14 at 04:18

0

Timestamps are stored as UTC values ad converted on retrieval to the time zone in use by the application. If you want British Columbia time set the time zone with SET time_zone = 'america/vancouver';

The manual reference is here

answered Mar 28 '14 at 04:18

not sure i am doing it right but i opned phpmyadmin and in the sql SQL tab tried to run the following command SET time_zone = 'america/vancouver'; but got the following error #1298 - Unknown or incorrect time zone: 'america/vancouver' ? – dev_marshell08 Mar 28 '14 at 04:27
1

The timezone strings won't work if you haven't installed them. If you're running on a hosted server your ISP would have to do it. You can use `set time_zone = '+08:00'` (or whatever your offset is), but that won't take account of daylight-saving time. Note that this setting applies on a per-connection basis. See the reference manual I linked to for a complete rundown. – Mar 28 '14 at 06:38

score 0 · Answer 2 · edited May 23 '17 at 11:49

use this one: refer this link:https://stackoverflow.com/a/6158432/3242978

-- Make sure we're all working off of the same local time zone
test=> SET timezone = 'America/Los_Angeles';
SET
test=> SELECT NOW();
              now              
-------------------------------
 2011-05-27 15:47:58.138995-07
(1 row)

test=> SELECT NOW() AT TIME ZONE 'UTC';
          timezone          
----------------------------
 2011-05-27 22:48:02.235541
(1 row)

update: 1

According to MySQL docs, the error #1193 occurs when you use wrong code for SQLSTATE.

Message: Unknown system variable %s

And, as you can see on the same page, the SQLSTATE 99003 is not defined.

can i run this command using phpmyadmin ? I tried it but got an error saying #1193 - Unknown system variable 'timezone' — dev_marshell08, Mar 28 '14 at 04:33

timestamp to use specific timezone?

2 Answers2