MySQL Forums :: General :: timestamp column behavior


Advanced Search

timestamp column behavior
Posted by: Ira Holtzer ()
Date: November 17, 2010 09:13AM

Hi,

Transaction data is stored to a DB. The transactions table has a TIMESTAMP column (transaction_ts) into which data is stored using this snippet (out of some larger SP):

INSERT INTO currency_transactions (transaction_type,
user_id,
game_type,
currency_type,
transaction_amount,
optional_transaction_amount,
transaction_balance,
transaction_status,
transaction_ts)
VALUES(tranType,
userId,
0,
currType,
currAmmount,
currCost,
userBalance,
1,
UTC_TIMESTAMP());

Connecting with workbench I performs the following:

SET @@session.time_zone='+00:00';
select @@time_zone;
> +00:00

select utc_timestamp();
> 2010-11-17 15:08:48

and yet when selecting data that just this instant was inserted via the above procedure I will get timestamps that are one hour before UTC. I.e. in our example here I'm getting a transaction that occurred at 2010-11-17 14:08:40.

I've accounted for display offset by session time_zone (at least I think that's what I did). I've accounted for storage format since UTC_TIMESTAMP() should not modify value in any way depending on server/session timezone.

How is this possible? What am I failing to consider?

Thanks,
DB

Options: ReplyQuote


Subject Written By Posted
timestamp column behavior Ira Holtzer 11/17/2010 09:13AM
Re: timestamp column behavior Rick James 11/18/2010 10:26PM


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.