Possible To Use A Different Timezone For A Default Timestamp Value?

[Greywacke]

hi,i've been reading up on fixing a timestamp problem i have in that it's in the host's US timezone.i was wondering if i could set a default value on a timestamp column to the following:

CONVERT_TZ(CURRENT_TIMESTAMP, 'SYSTEM', '+2:00')

but phpmysql will not seem to accept it.will i be able to do this if i ask the host to change this on console level perhaps?

[Greywacke]

this SQL string sets the default value to be 0000-00-00 00:00:00.is there anyway to modify the following query to add a function as a mysql default?

ALTER TABLE `8_transactions` CHANGE `timestamp_TransactionEvent` `timestamp_TransactionEvent` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NULL DEFAULT 'CONVERT_TZ(CURRENT_TIMESTAMP, ''SYSTEM'', ''+2:00'')' COMMENT 'TimeStamp Transaction Event, Current TimeStamp No Keys;

[Greywacke]

well, trying to input the sequel query manually i get the following problem:

ALTER TABLE '8_transactions' CHANGE 'timestamp_TransactionEvent' 'timestamp_TransactionEvent' TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NULL DEFAULT 'CONVERT_TZ(CURRENT_TIMESTAMP, "SYSTEM", "+2:00")' COMMENT 'TimeStamp Transaction Event,Current TimeStamp No Keys'MySQL said: Documentation#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''8_transactions' CHANGE 'timestamp_TransactionEvent' 'timestamp_TransactionEvent' at line 1

[Greywacke]

lets see if this works:

ALTER TABLE '8_transactions' CHANGE 'timestamp_TransactionEvent' 'timestamp_TransactionEvent' TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NULL DEFAULT 'CONVERT_TZ(CURRENT_TIMESTAMP, "SYSTEM", "+2:00")' COMMENT 'TimeStamp Transaction Event,Current TimeStamp No Keys'

same error... mysql keeps removing the space after the name and before the variable type.edited it again and this is the error i get:

ALTER TABLE `8_transactions` CHANGE `timestamp_TransactionEvent` `timestamp_TransactionEvent` TIMESTAMP ON UPDATE DEFAULT 'CONVERT_TZ(CURRENT_TIMESTAMP, "SYSTEM", "+2:00")'MySQL said: Documentation#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT 'CONVERT_TZ(CURRENT_TIMESTAMP, "SYSTEM", "+2:00")'' at line 1

is it at all possible to set a default value such as this? if it is, how should i be escaping the characters, and which ones in the default value?

[justsomeguy]

I'm not sure if you pasted something other than what you're running, but make sure you're using the right quote types. Single and double quotes are only for quoting strings, not table or field names. Backquotes are for table and field names. Your query shows single quotes around the tables and fields, but the error message has them replaced with backquotes.