phpmyadmin Question posted in
The official documentation can be found here.

How to set default value for datetime column in MYSQL? – Phpmyadmin

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.

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    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.


  2. 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';
    Login or Signup to reply.
  3. 0

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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search