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.
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())
If 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;
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.
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:
INSERT INTO servers (server_name, online_status, exchange, disk_space, network_shares)
VALUES('m1','ONLINE','exchange','disk_space','network_shares', NOW())
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 ])
Yes, you can use the CURRENT_TIMESTAMP()
command.
See here: Date and Time Functions
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.
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)
$rs = $db->Insert('register',"'$fn','$ln','$email','$pass','$city','$mo','$fil'","'f_name','l_name=','email','password','city','contact','image'");
Source: Stackoverflow.com