I have a web app, that logging some events (not important what). My web server is in different timezone , i want to log date and time of my timezone.
I created simple DB table with fields:
ID - BIGINT
(auto increment), primary key
WH - DATETIME
(this is the problematic one)
LOG_TEXT VARCHAR(255)
(text of event)
field WH is the problematic, i can set only CURRENT_TIMESTAMP
, but showing 6 hours less than my current timezone time.
So i tried to set up default value for field WH (in phpMyAdmin) , with the following: CONVERT_TZ(WH,@@global.time_zone,'+01:00')
phpMyAdmin shows me the following error:
#1067 - invalid default value for 'WH'
any help how to set up different timezone for CURRENT_TIMESTAMP ?
PS: I googled a lot, no answer that matches for that question PPS: I cannot change timezone on my server, it's a shared hosting and it's simply not possible.
thanks in advance for your valuable comments.
So , this is the way i solved my problem:
WH
was incremented by 6hours. I know it's not the best option,but all of my stuff is being processed in the same timezone (UTC+6)Now correct timestamp data is stored in this view, however the original LOG
table has system timezone timestamp. Field WH
in table LOG
has default value CURRENT_TIMESTAMP
Now i can view my logs by selecting values from V_LOG
.
SQL:
CREATE ALGORITHM=UNDEFINED DEFINER=`myuser`@`localhost` SQL SECURITY DEFINER VIEW `V_LOG`
AS
SELECT ID, DATE_ADD(WH,interval 6 HOUR) AS WH,LOG_TEXT FROM LOG;
I was unable to use CONVERT_TZ with @@global.time_zone, because views cannot contain parameters.