How to get current date & time in MySQL?
MysqlSqlDatetimeMysql Problem Overview
Is there a value or command like DATETIME that I can use in a manual query to insert the current date and time?
INSERT INTO servers (
server_name, online_status, exchange, disk_space, network_shares
) VALUES(
'm1', 'ONLINE', 'ONLINE', '100GB', 'ONLINE' 'DATETIME'
)
The quoted DATETIME value at the end is where I want to add the current date and time.
Mysql Solutions
Solution 1 - Mysql
You can use NOW()
:
INSERT INTO servers (server_name, online_status, exchange, disk_space, network_shares, c_time)
VALUES('m1', 'ONLINE', 'exchange', 'disk_space', 'network_shares', NOW())
Solution 2 - Mysql
Use CURRENT_TIMESTAMP() or now()
Like
INSERT INTO servers (server_name, online_status, exchange, disk_space,
network_shares,date_time) VALUES('m1','ONLINE','ONLINE','100GB','ONLINE',now() )
or
INSERT INTO servers (server_name, online_status, exchange, disk_space,
network_shares,date_time) VALUES('m1', 'ONLINE', 'ONLINE', '100GB', 'ONLINE'
,CURRENT_TIMESTAMP() )
Replace date_time with the column name you want to use to insert the time.
Solution 3 - Mysql
Even though there are many accepted answers, I think this way is also possible:
Create your 'servers' table as following :
CREATE TABLE `servers`
(
id int(11) NOT NULL PRIMARY KEY auto_increment,
server_name varchar(45) NOT NULL,
online_status varchar(45) NOT NULL,
_exchange varchar(45) NOT NULL,
disk_space varchar(45) NOT NULL,
network_shares varchar(45) NOT NULL,
date_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
);
And your INSERT statement should be :
INSERT INTO servers (server_name, online_status, _exchange, disk_space, network_shares)
VALUES('m1', 'ONLINE', 'ONLINE', '100GB', 'ONLINE');
My Environment:
Core i3 Windows Laptop with 4GB RAM, and I did the above example on MySQL Workbench 6.2 (Version 6.2.5.0 Build 397 64 Bits)
Solution 4 - Mysql
Yes, you can use the CURRENT_TIMESTAMP()
command.
See here: Date and Time Functions
Solution 5 - Mysql
The correct answer is SYSDATE().
INSERT INTO servers (
server_name, online_status, exchange, disk_space,
network_shares, date_time
)
VALUES (
'm1', 'ONLINE', 'ONLINE', '100GB', 'ONLINE', SYSDATE()
);
We can change this behavior and make NOW()
behave in the same way as SYSDATE()
by setting sysdate_is_now command line argument to True
.
Note that NOW()
(which has CURRENT_TIMESTAMP()
as an alias), differs from SYSDATE()
in a subtle way:
> SYSDATE() returns the time at which it executes. This differs from the > behavior for NOW(), which returns a constant time that indicates the > time at which the statement began to execute. (Within a stored > function or trigger, NOW() returns the time at which the function or > triggering statement began to execute.)
As indicated by Erandi, it is best to create your table with the DEFAULT
clause so that the column gets populated automatically with the timestamp when you insert a new row:
date_time datetime NOT NULL DEFAULT SYSDATE()
If you want the current date in epoch format, then you can use UNIX_TIMESTAMP(). For example:
select now(3), sysdate(3), unix_timestamp();
would yield
+-------------------------+-------------------------+------------------+
| now(3) | sysdate(3) | unix_timestamp() |
+-------------------------+-------------------------+------------------+
| 2018-11-27 01:40:08.160 | 2018-11-27 01:40:08.160 | 1543282808 |
+-------------------------+-------------------------+------------------+
Related:
Solution 6 - Mysql
INSERT INTO servers (server_name, online_status, exchange, disk_space, network_shares)
VALUES('m1','ONLINE','exchange','disk_space','network_shares', NOW())
Solution 7 - Mysql
In database design, iIhighly recommend using Unixtime for consistency and indexing / search / comparison performance.
UNIX_TIMESTAMP()
One can always convert to human readable formats afterwards, internationalizing as is individually most convenient.
FROM_ UNIXTIME (unix_timestamp, [format ])
Solution 8 - Mysql
İf you make change default value to CURRENT_TIMESTAMP
it is more effiency,
ALTER TABLE servers MODIFY COLUMN network_shares datetime NOT NULL DEFAULT CURRENT_TIMESTAMP;
Solution 9 - Mysql
You can use not only now()
, also current_timestamp()
and localtimestamp()
.
The main reason of incorrect display timestamp is inserting NOW() with single quotes! It didn't work for me in MySQL Workbench because of this IDE add single quotes for mysql functions and i didn't recognize it at once )
Don't use functions with single quotes like in MySQL Workbench. It doesn't work.
Solution 10 - Mysql
$rs = $db->Insert('register',"'$fn','$ln','$email','$pass','$city','$mo','$fil'","'f_name','l_name=','email','password','city','contact','image'");