How do I add a default timestamp in upgrade.php?

How do I add a default timestamp in upgrade.php?

by tim st.clair -
Number of replies: 2
Picture of Plugin developers

How can I add a default to a field that is a timestamp, so that if updates to the record don't include the column, it is automatically populated by the database engine? In mysql, the create for the column would be:

`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'event timestamp'

In the install.xml file, timestamp isn't an allowed type so it uses datetime.

<FIELD NAME="time" TYPE="datetime" NOTNULL="true" DEFAULT="CURRENT_TIMESTAMP" SEQUENCE="false"/>

XMLDB editor warns about this if the type is set to timestamp, even though lib/xmldb/xmldb_field.php seems to show knowledge of the  XMLDB_TYPE_TIMESTAMP field type.

I used xmldb editor to generate the php. This generates the column as type "datetime", with the default set at "current_timestamp". Obviously it's getting this from the install.xml file.

  $table->add_field('time', XMLDB_TYPE_DATETIME, null, null, XMLDB_NOTNULL, null, 'CURRENT_TIMESTAMP');

 When upgrade.php is executed, it throws an error:

Debug info: Invalid default value for 'time'
time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

I'm having to create it without a default, then go in and alter the table by hand. Surely there's an official way?

Average of ratings: -
In reply to tim st.clair

Re: How do I add a default timestamp in upgrade.php?

by Tim Hunt -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

You don't.

I know it is a pity not to take advantage of something the database could do for you automatically, but because Moodle has to work on four different database engines, and they all handle dates slightly differently, we just set all dates in PHP.

If you make the column NOT NULL without a default, then at least you will get an error if any function fails to set it.

Average of ratings: -
In reply to Tim Hunt

Re: How do I add a default timestamp in upgrade.php?

by tim st.clair -
Picture of Plugin developers
Ok, good answer.

I guess I just expected that since there were already specific generators for the databases platforms in lib/ddl/ that something like timestamps would be able to be handled at the lower level automatically. But I've hit the same answer before when I looked for support for views, stored procedures, triggers,multiple statements, and all those goodies that make things easier... I'll go back to wishing (and poking about in the generators and dml objects ... smile

Average of ratings: -