How to get current date & time in MySQL?

Question

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.

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html — Colandus, Oct 08 '13 at 11:19
NOW(), see https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_now ... And please, next time put at least some research effort into your question, this can be easily googled (therefore I just downvoted this question) — dirkk, Oct 08 '13 at 11:19
My question was more aimed at how I should use it in the syntax I posted above. — Samuel Nicholson, Oct 08 '13 at 11:21
See franciscos answer for that. But this is very, very basic, it is just a function call. Therefore, again, you should put more research effort into the issue before posting a question and also you might want to read some instructions on SQL. — dirkk, Oct 08 '13 at 11:23
ironic. when putting effort into finding the answer you get to ... this answer! — Zach Smith, Jul 12 '14 at 22:18
@dirkk I agree with you about the research effort. But I think it's a valid question because if you would type `INSERT INTO sometable (\`datetime\`) VALUES ( NOW() ) ` in phpmyadmin's sql tab then phpmyadmin's hint would show an error icon with the message: `A comma or a closing bracket was expected near (`. This is confusing, because the sql query is valid but phpmyadmin says it's not. — Julian, Feb 21 '19 at 10:13

score 305 · Accepted Answer · edited Jan 26 '18 at 09:35

305

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())

edited Jan 26 '18 at 09:35

Yuri

3,082
3
28
47

answered Oct 08 '13 at 11:19

francisco.preller

6,559
4
28
39

1

#1054 - Unknown column 'stamp' in 'field list' - Is there something I'm missing? – Samuel Nicholson Oct 08 '13 at 11:22
52

Just replace stamp with whatever your field name is, really. DOn't just copy and paste. – francisco.preller Oct 08 '13 at 11:24
13

If it's a "date" field type, you can also use `curdate()` – tandy Aug 23 '15 at 02:35

score 34 · Answer 2 · answered Oct 08 '13 at 11:21

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.

score 30 · Answer 3 · edited Jan 03 '18 at 15:44

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)

score 7 · Answer 4 · edited Jan 03 '18 at 15:43

7

Yes, you can use the CURRENT_TIMESTAMP() command.

See here: Date and Time Functions

edited Jan 03 '18 at 15:43

simhumileco

31,877
16
137
115

answered Oct 08 '13 at 11:20

Alec.

5,371
5
34
69

codeforester · Answer 5 · 2020-06-22T22:38:37.920

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 |
+-------------------------+-------------------------+------------------+

Difference between NOW(), SYSDATE() & CURRENT_DATE() in MySQL

score 2 · Answer 6 · edited Feb 18 '17 at 12:14

2

INSERT INTO servers (server_name, online_status, exchange, disk_space, network_shares)
VALUES('m1','ONLINE','exchange','disk_space','network_shares', NOW())

edited Feb 18 '17 at 12:14

AT82

71,416
24
140
167

answered Feb 18 '17 at 11:55

Rajesh Chaurasia

21
1

score 1 · Answer 7 · edited Sep 12 '16 at 18:21

1

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 ])

edited Sep 12 '16 at 18:21

The One and Only ChemistryBlob

7,864
10
36
69

answered Feb 27 '16 at 15:24

tony gil

9,424
6
76
100

score 1 · Answer 8 · answered Mar 27 '17 at 11:38

1

İ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;

answered Mar 27 '17 at 11:38

Ferhat KOÇER

3,890
1
26
26

score 0 · Answer 9 · edited Sep 08 '14 at 19:04

0

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.

edited Sep 08 '14 at 19:04

BenMorel

34,448
50
182
322

answered Aug 07 '14 at 09:15

obohovyk

482
2
5
12

score -9 · Answer 10 · edited May 21 '14 at 06:34

-9

$rs = $db->Insert('register',"'$fn','$ln','$email','$pass','$city','$mo','$fil'","'f_name','l_name=','email','password','city','contact','image'");

edited May 21 '14 at 06:34

웃웃웃웃웃

11,829
15
59
91

answered May 21 '14 at 06:07

user3656636

5
1

You should shortly explain your answer. – Sebastian Zartner May 21 '14 at 06:33
This answer means very little indeed! – Matteo Tassinari May 21 '14 at 06:40

How to get current date & time in MySQL?

10 Answers10

Linked

Related