6

I have a MySQL table with some dates, I need that one of them have a default value equal to current time, I'm using 'Sequel Pro' to build the database, Then i wrote 'now()' (and 'GETDATE()') in default value, but doesn't work.

Can someone do help me, How 'Set Default value' to 'NOW()' with Sequel PRO?

ERROR:

An error occurred when trying to change the field 'DataDoPedido' via

ALTER TABLE Reserva CHANGE DataDoPedido DataDoPedido DATE NOT NULL DEFAULT 'now()'

MySQL said: Invalid default value for 'DataDoPedido'

thanks.

Rui Martins
  • 1,503
  • 1
  • 22
  • 30

1 Answers1

10

For MySQL, the DEFAULT specified for a column must be a constant; it cannot be the return from a function. The one exception to this is the TIMESTAMP datatype, which can have a DEFAULT CURRENT_TIMESTAMP.

If you need to initialize a DATE column, one workaround is to create a BEFORE INSERT ON trigger.

spencer7593
  • 99,718
  • 14
  • 99
  • 122
  • Thanks. Can i use TIMESTAMP like DATE in PHP? I changed DATE to TIMESTAMP on database and worked, but i never used TIMESTAMP, what is the difference to 'date'? – Rui Martins Nov 24 '13 at 04:07
  • 1
    The `DATE` datatype in MySQL has no time portion, it's only year-month-day, so when that gets cast to a `DATETIME` or `TIMESTAMP`, the time component is considered to be midnight 00:00:00. The `DATETIME` and `TIMESTAMP` datatypes hold date AND time with resolution down to a second. With the MySQL JDBC driver, the DATE, DATETIME and TIMESTAMP are basically interchangeable. Not sure about PHP. – spencer7593 Nov 24 '13 at 21:55