The way MySQL implements the TIMESTAMP data type, it is actually storing the epoch time in the database. So you could just use a TIMESTAMP column with a default of CURRENT_TIMESTAMP and apply the UNIX_TIMESTAMP() to it if you want to display it as an int:
CREATE TABLE foo(
created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
insert into foo values (current_Date()),(now());
select unix_timestamp(created) from foo;
+-------------------------+
| unix_timestamp(created) |
+-------------------------+
| 1300248000 |
| 1300306959 |
+-------------------------+
2 rows in set (0.00 sec)
However, if you really want the datatype of the column to be INT, you can use R. Bemrose's suggestion and set it via trigger:
CREATE TABLE foo(
created INT NULL
);
delimiter $$
create trigger tr_b_ins_foo before insert on foo for each row
begin
if (new.created is null)
then
set new.created = unix_timestamp();
end if;
end $$
delimiter ;
insert into foo values (unix_timestamp(current_Date())), (null);
select created from foo;
+------------+
| created |
+------------+
| 1300248000 |
| 1300306995 |
+------------+
2 rows in set (0.00 sec)
The way MySQL implements the TIMESTAMP data type, it is actually storing the epoch time in the database. So you could just use a TIMESTAMP column with a default of CURRENT_TIMESTAMP and apply the UNIX_TIMESTAMP() to it if you want to display it as an int:
CREATE TABLE foo(
created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
insert into foo values (current_Date()),(now());
select unix_timestamp(created) from foo;
+-------------------------+
| unix_timestamp(created) |
+-------------------------+
| 1300248000 |
| 1300306959 |
+-------------------------+
2 rows in set (0.00 sec)
However, if you really want the datatype of the column to be INT, you can use R. Bemrose's suggestion and set it via trigger:
CREATE TABLE foo(
created INT NULL
);
delimiter $$
create trigger tr_b_ins_foo before insert on foo for each row
begin
if (new.created is null)
then
set new.created = unix_timestamp();
end if;
end $$
delimiter ;
insert into foo values (unix_timestamp(current_Date())), (null);
select created from foo;
+------------+
| created |
+------------+
| 1300248000 |
| 1300306995 |
+------------+
2 rows in set (0.00 sec)
From the documentation:
With one exception, 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.
Cannot use default with `ON UPDATE UNIX_TIMESTAMP()`
unix timestamp - How to set default value of a mysql column as unix_timestamp? - Stack Overflow
MYSQL Auto Create Unix Timestamp
mysql - How to store unix timestamp as int with default and on update? - Stack Overflow
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 !
Note : I have no idea about the performance of MYSQL TRIGGER
Please go through these links
Identify some of the drawback of implementing sql server triggers
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());
Hi,
Currently I have a column of type char that stores unix time with microseconds as follows:
`last_update_utime` char(18) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Unix Time of Last Update with mseconds'
I update that column as below;
UPDATE `transaction_tbl`
SET `transaction_token` = '$token',
`last_update_utime` = UNIX_TIMESTAMP(LOCALTIMESTAMP(6))
WHERE `user_id` = '$userID'
I use the field last_update_utime to address concurrency.
What is the correct column type for unix timestamps with microseconds ?
i have seen posts on the web stating int(11) or bigint(20)
but the issue of microseconds is not addressed ?
Is keeping the column type to 'char' sufficient or better to use something like decimal(18,6) ?
Pls advise.
A Unix timestamp is a large integer (the number of seconds since 1970-01-01 00:00:00 UTC), so INT(11) is the correct datatype.
Unfortunately, I don't think there's any way to specify a default that will insert the current timestamp. You'll need to call UNIX_TIMESTAMP() explicitly when inserting, and use that. Function calls aren't allowed in DEFAULT specifications.
Actually, you have to use either bigint or varchar because the maximum for int(11) is 2'147'483'647 (more info here).
Then, as the previous answers say, you have to manually insert UNIX_TIMESTAMP()