MySQL Forums :: Newbie :: converting timezone for data in specific table


Advanced Search

converting timezone for data in specific table
Posted by: B A ()
Date: January 18, 2012 12:54PM

hi,
I have price data for the Japanese stock market, but the time and date stamp on it is Eastern Time. I need to convert it into JST (I believe that is the abbreviation for Japan time zone), so I can work with the data representing the Japan date and time.
I extract the data from the db to create a flat file, using the following SQL statement:
SELECT contract_name, trade_date,trade_time, trade_price, trade_volume FROM $tblname where contract_name='$first_contract' and DAYOFWEEK(trade_date)!=1 AND trade_date<'$first_dt';

the structure of my table is as follows:

mysql> desc price_hist;
+---------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-----------------------+------+-----+---------+----------------+
| trade_id | int(11) | NO | PRI | NULL | auto_increment |
| contract_name | varchar(14) | NO | MUL | NULL | |
| trade_date | date | NO | | NULL | |
| trade_time | varchar(6) | NO | | NULL | |
| trade_price | decimal(10,4) | NO | | NULL | |
| trade_volume | bigint(20) | NO | | NULL | |
+---------------+-----------------------+------+-----+---------+----------------+
8 rows in set (0.02 sec)
mysql> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM | SYSTEM |
+--------------------+---------------------+
1 row in set (0.00 sec)

I also tried the following to test, but no success:
select convert_tz("2010-06-30 19:00:00",'CST','JST');
+-----------------------------------------------+
| convert_tz("2010-06-30 19:00:00",'CST','JST') |
+-----------------------------------------------+
| NULL |
+-----------------------------------------------+
1 row in set (0.01 sec)

Can you please tell me how I can convert the date and time to get the date and time in Japan time. thanks!



Edited 1 time(s). Last edit at 01/18/2012 01:02PM by B A.

Options: ReplyQuote


Subject Written By Posted
converting timezone for data in specific table B A 01/18/2012 12:54PM
Re: converting timezone for data in specific table Peter Brawley 01/18/2012 01:16PM
Re: converting timezone for data in specific table Rick James 01/21/2012 01:40AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.