Update column based on input from another column 3

[penguinspeaks]

In my mysql DB, I have a field (current_) that automatically defaults as the current date/time. This happens each time there is a new entry.
I have another field (next_) that I want to be the current date/time + 4 hours.
I have tried this using code(classic asp) as well as using unix time. Because asp cannot format to yyyy-mm-dd as it is in the DB, this won't work. I cannot use unix time because classic asp has a difficult time converting back to standard date/time from UNIX time.

If there a way to have this script nested, OR RUN AS A STORED PROCEDURE in the DB??


Thanks,
Penguin
Please keep in mind that I am using classic ASP with MySQL database. Not sure if that information helps or not.

 

[feherke]

Hi

The bigger question would be your MySQL's version. If it is 8.0.13 or newer, you can use expression in the [tt]default[/tt] clause :

[b]create table[/b] penguinspeaks [teal]([/teal]
    current_ [maroon]datetime[/maroon] [b]default[/b] current_timestamp[teal],[/teal]
    next_ [maroon]datetime[/maroon] [b]default[/b] [teal]([/teal]current_timestamp [teal]+[/teal] interval [purple]4[/purple] hour[teal])[/teal] [silver]-- parenthesis mandatory for expressions[/silver]
[teal]);[/teal]

If you have older MySQL, you will have to use a trigger :

[b]create trigger[/b] penguinspeaks_next
[b]before[/b] [b]insert on[/b] penguinspeaks [b]for[/b] [b]each[/b] [b]row[/b] 
[b]set[/b] new[teal].[/teal]next_ [teal]=[/teal] current_timestamp [teal]+[/teal] interval [purple]4[/purple] hour[teal];[/teal]

Feherke.
feherke.github.io

 

[Andrzejek]

I would use a virtual column instead. No code needed, no trigger needed, just set it up and forget about it...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[feherke]

Hi

Thank you, Andy. Somehow I completely missed them until now.


Feherke.
feherke.github.io

 

[Andrzejek]

... and once you know about them (virtual columns that is) you will ask yourself: "How could I've lived without them...?"

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[feherke]

Hi

... and once you know about them (virtual columns that is) you will ask yourself: "How could I've lived without them...?"

Exactly that is the most maddening in it : I knew the feature for about 9 years from MSSQL's computed columns.

Feherke.
feherke.github.io

 

[penguinspeaks]

Thanks for the ideas. I had to read up on virtual columns, but it solved my issues.

Thanks,
Penguin
Please keep in mind that I am using classic ASP with MySQL database. Not sure if that information helps or not.