Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to set default value of a mysql column as unix_timestamp?

I was thinking at something like this:

`post_modified` int(11) NOT NULL DEFAULT UNIX_TIMESTAMP (NOW ()) ON UPDATE UNIX_TIMESTAMP (NOW ()),

But this code it is not working.

like image 960
m3tsys Avatar asked Oct 29 '12 15:10

m3tsys


People also ask

How do I change the default value for a column in MySQL?

A column's default value is part of its definition, but can be modified separately from other aspects of the definition. To change a default value, use ALTER col_name SET DEFAULT : ALTER TABLE mytbl ALTER j SET DEFAULT 1000; Default values must be constants.

What is Unix_timestamp in MySQL?

UNIX_TIMESTAMP() : This function in MySQL helps to return a Unix timestamp. We can define a Unix timestamp as the number of seconds that have passed since '1970-01-01 00:00:00'UTC. Even if you pass the current date/time or another specified date/time, the function will return a Unix timestamp based on that.

What is the default value of a column in MySQL?

For numeric types, the default is 0 , with the exception that for integer or floating-point types declared with the AUTO_INCREMENT attribute, the default is the next value in the sequence. For date and time types other than TIMESTAMP , the default is the appropriate “zero” value for the type.

How do I add a timestamp to a column in MySQL?

You can use the timestamp column as other posters mentioned. Here is the SQL you can use to add the column in: ALTER TABLE `table1` ADD `lastUpdated` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ; This adds a column called 'lastUpdated' with a default value of the current date/time.


2 Answers

Note : I have no idea about the performance of MYSQL TRIGGER

Please go through these links

  1. Identify some of the drawback of implementing sql server triggers

  2. Using Triggers

You can create triggers for this.

for insertion

CREATE TRIGGER {trigger_name} BEFORE INSERT ON {table_name} FOR EACH ROW SET new.{field_name} = UNIX_TIMESTAMP(NOW());

for update

CREATE TRIGGER {trigger_name} BEFORE UPDATE ON {table_name} FOR EACH ROW SET new.{field_name} = UNIX_TIMESTAMP(NOW());

like image 94
Arjun Raj Avatar answered Oct 05 '22 17:10

Arjun Raj


Nope you cant use it as you showed.

From Data Type doc on MySql:

"The default value must be a constant; it cannot be a function or an expression. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE. The exception is that you can specify CURRENT_TIMESTAMP as the default for a TIMESTAMP column."

Look at below answer for workaround:

Is it possible to create a column with a UNIX_TIMESTAMP default in MySQL?

Hope this will help !

like image 39
metalfight - user868766 Avatar answered Oct 05 '22 18:10

metalfight - user868766