1

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.

FeHora
  • 336
  • 5
  • 17
  • 1
    Long term you are almost always better off storing at UTC and then changing the time zone to your local one when pulling the data back out. What if you move? What if the server moves? Or just goes down for a few hours? Using UTC always will prevent unexpected issues due to things like that. – Elin Apr 01 '19 at 12:27

3 Answers3

0

In mysql configuration file (my.cnf) set below line and restart mysql

default-time-zone='+01:00'

Or login mysql with root and enter below query

SET GLOBAL time_zone = '+1:00';
SachinPatil4991
  • 774
  • 6
  • 13
  • i know about that, but as i mentioned, i am using shared hosting , so i have no root privileges. Simply - i cannot change system settings for mysql. Only way to write different timezone to my DB is using CONVERT_TZ.. I can select values in my timezone using that convert command, but i think is much simpler to use correct default value in my DB table, than complicating things with selects. I think i will create a `VIEW` where i will convert timezone – FeHora Apr 01 '19 at 12:25
0

You can create TRIGGER on BEFORE INSERT for updating the date and time

Sarath
  • 1
-1

So , this is the way i solved my problem:

  • Created a view with the same fields, but field 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 WHin 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.

FeHora
  • 336
  • 5
  • 17