Current Date and Time for Default Value

Archived Topics
1

How do you make the current time and date a default value in MySQL? Also, what is the default syntax or mask for a DateTime field?

2

MySQL recognizes CURRENT_TIMESTAMP or now() to get the current date and time. You cannot set the default value to a function, however. The way you deal with this is by setting time=now() as part of your INSERT or UPDATE query. What exactly are you trying to do?

The default syntax for a datetime is: yyyy-MM-dd H:mm:s as defined here

3

If you really really want to have your datetime inserted by default in your table you can do so with a trigger:

CREATE TRIGGER trigger_name BEFORE INSERT ON table_name FOR EACH ROW SET NEW.column_name=CURRENT_TIMESTAMP;

Adjust the appropriate values (table name, column, etc). Not exactly the most convenient solution, but it’ll work. It’s a bit silly that MySQL doesn’t support this, but what can you do…

In general, I would just do what Nathan suggested and use CURRENT_TIMESTAMP in your insert/update query.

Regards,

4

I’m creating data entry screens for the operators and I would like the time stamp automated to eliminate any entry errors.

5
  1. Create input objects. Here we have a text area named TextArea and a button.
  2. Prepare your INSERT query. The MySQL Query Browser can help you formulate it.
  3. On the ActionPerformed event on the button, run the following Jython Code. We will use the function fpmi.db.runPrepStmt() (requires 1.7.0 or newer) because it will protect user input from breaking the syntax of the query. You could have also used fpmi.db.runUpdateQuery().
# This code would gather some user entered text and insert it into the database.

userText = event.source.parent.getComponent("TextArea").text #your path may be different
userName = fpmi.security.getUsername()
fpmi.db.runPrepStmt("INSERT INTO Comments (Name, UserComment, t_stamp) VALUES (?,?,CURRENT_TIMESTAMP)", [userName, userText])