Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Update column based on input from another column 3

Status
Not open for further replies.
penguinspeaks

penguinspeaks

Technical User
Nov 13, 2002
234
US
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.
 
Sort by date Sort by votes
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 :
Code: 
[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 :
Code: 
[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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
... and once you know about them (virtual columns that is) you will ask yourself: "How could I've lived without them...?" [smile2]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

boylesg
  • Locked
  • Question
Can't sort my table by a particular column, records are returned in autinc order.
Replies
4
Views
668
spamjim
spamjim
BobThornton
  • Locked
  • Question
Drop/Truncate tables based on a stored procedures value 1
Replies
6
Views
995
BobThornton
BobThornton
jawadfrhad
  • Locked
  • Question
how make row to column
Replies
4
Views
363
jawadfrhad
jawadfrhad
EdLentz
  • Locked
  • Question
Looking for suggestions on how to do this
Replies
4
Views
597
Chris Miller
Chris Miller
AbidingDude
  • Locked
  • Question
One Constraint for Multiple Columns 1
Replies
2
Views
465
AbidingDude
AbidingDude

Part and Inventory Search

Sponsor

Back
Top