Insert current date in datetime format mySQL

Question

I'm having problems getting the date inserted properly into my database.

$date = date('m/d/Y h:i:s', time());

I use this format, and, it echoes out correctly, however, when, I insert

mysql_query("INSERT INTO table 
(dateposted) 
VALUES ('$date')");

it doesn't appear to work successfully, and, the time remains 00:00:00 If you could find the solution that would be great, thanks.

Dateposted is the datetime format. – CCates Mar 02 '12 at 22:09 — CCates, Mar 02 '12 at 22:09

Aaron W. · Accepted Answer · 2012-03-02T22:26:29.527

254

If you're looking to store the current time just use MYSQL's functions.

mysql_query("INSERT INTO `table` (`dateposted`) VALUES (now())");

If you need to use PHP to do it, the format it Y-m-d H:i:s so try

$date = date('Y-m-d H:i:s');
mysql_query("INSERT INTO `table` (`dateposted`) VALUES ('$date')");

edited Mar 02 '12 at 22:26

answered Mar 02 '12 at 22:11

Aaron W.

9,254
2
34
45

22

But it'll use MySQL server locale/timezone. If U have Apache/PHP and MySQL on different servers and timezone set incorrect in one of them U'll have different values. – Jeff_Alieffson Nov 24 '13 at 17:57
3

@Jeff_Alieffson is right. Using `UTC_TIMESTAMP()` (https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_utc-timestamp) is preferred instead of `NOW()` – Ejaz Mar 31 '16 at 17:53
I tried same but I am getting 0000-00-00 00:00:00 and my datatype is datetime. any help in this? – user9437856 Aug 06 '18 at 02:39
Would be great to use PDO instead of mysql_query(), wouldn't? – DaWe Aug 25 '20 at 10:06

score 39 · Answer 2 · answered Mar 02 '12 at 22:11

39

Try this instead

$date = date('Y-m-d H:i:s');

answered Mar 02 '12 at 22:11

Erik Giberti

1,235
9
11

score 8 · Answer 3 · edited Jan 28 '14 at 21:17

8

NOW() is used to insert the current date and time in the MySQL table. All fields with datatypes DATETIME, DATE, TIME & TIMESTAMP work good with this function.

YYYY-MM-DD HH:mm:SS

Demonstration:

Following code shows the usage of NOW()

INSERT INTO auto_ins
(MySQL_Function, DateTime, Date, Time, Year, TimeStamp)
VALUES
(“NOW()”, NOW(), NOW(), NOW(), NOW(), NOW());

edited Jan 28 '14 at 21:17

reigeki

391
1
5
19

answered Oct 19 '12 at 06:02

Gaurang

1,928
18
12

score 7 · Answer 4 · answered Jun 20 '13 at 21:25

7

you can use CURRENT_TIMESTAMP, mysql function

answered Jun 20 '13 at 21:25

Diar Selimi

71
1
1

score 6 · Answer 5 · edited May 21 '15 at 12:06

6

set the type of column named dateposted as DATETIME and run the following query:

INSERT INTO table (`dateposted`) VALUES (CURRENT_TIMESTAMP)

edited May 21 '15 at 12:06

Sufiyan Ghori

18,164
14
82
110

answered Mar 04 '14 at 13:53

Abdullah

488
6
16

score 2 · Answer 6 · answered Mar 02 '12 at 22:10

2

"datetime" expects the date to be formated like this: YYYY-MM-DD HH:MM:SS

so format your date like that when you are inserting.

answered Mar 02 '12 at 22:10

Jan Hančič

53,269
16
95
99

So I would do date("YYYY/MM/DD HH:MM:SS", time());? – CCates Mar 02 '12 at 22:11
1

No? use dashes(-) not slashes (/). – Jan Hančič Mar 02 '12 at 22:12

score 1 · Answer 7 · answered Sep 24 '15 at 18:36

1

     <?php $date= date("Y-m-d");
$time=date("H:m");
$datetime=$date."T".$time;
mysql_query(INSERT INTO table (`dateposted`) VALUES ($datetime));
?>

<form action="form.php" method="get">
<input type="datetime-local" name="date" value="<?php echo $datetime; ?>">
<input type="submit" name="submit" value="submit">

answered Sep 24 '15 at 18:36

PratapSingh hajari

11
2

1

Welcome to Stack Overflow! Good answers *explain* as well as provide code. Consider updating your answer to include an explanation about how this code works and why it is the best option. – Ajean Sep 25 '15 at 00:59

score 1 · Answer 8 · answered Apr 26 '16 at 11:15

If you Pass date from PHP you can use any format using STR_TO_DATE() mysql function . Let conseder you are inserting date via html form

$Tdate = "'".$_POST["Tdate"]."'" ;    //   10/04/2016
$Tdate = "STR_TO_DATE(".$Tdate.", '%d/%m/%Y')"  ;  
mysql_query("INSERT INTO `table` (`dateposted`) VALUES ('$Tdate')");

The dateposted should be mysql date type . or mysql will adds 00:00:00
in some case You better insert date and time together into DB so you can do calculation with hours and seconds . () .

$Tdate=date('Y/m/d H:i:s') ; // this to get current date as text .
$Tdate = "STR_TO_DATE(".$Tdate.", '%d/%m/%Y %H:%i:%s')"  ;

score 1 · Answer 9 · answered Nov 26 '16 at 10:37

The best way to make it easier and efficient is this:

CREATE TABLE table_name (
field1,
..
..
time_updated timestamp default current_timestamp
)

Now, when you insert a row into table, you can skip this field (time_updated) as it will fill with current time as default value

score 0 · Answer 10 · answered Jun 19 '17 at 17:11

0

Just use with your timezone :

date_default_timezone_set('Asia/Kolkata');      
$date=date("Y/m/d h:i:sa");

answered Jun 19 '17 at 17:11

score 0 · Answer 11 · answered Nov 11 '18 at 01:27

0

$date = date('Y-m-d H:i:s'); with the type: 'datetime' worked very good for me as i wanted to print whole date and timestamp..

$date = date('Y-m-d H:i:s'); $stmtc->bindParam(2,$date);

answered Nov 11 '18 at 01:27

Rehan

11
3

score 0 · Answer 12 · answered Sep 25 '20 at 14:27

It depends on what datatype you set for your db table.

DATETIME (datatype)

MYSQL

INSERT INTO t1 (dateposted) VALUES ( NOW() )
// This will insert date and time into the col. Do not use quote around the val

PHP

$dt = date('Y-m-d h:i:s');
INSERT INTO t1 (dateposted) VALUES ( '$dt' )
// This will insert date into the col using php var. Wrap with quote.

DATE (datatype)

MYSQL

INSERT INTO t1 (dateposted) VALUES ( NOW() )
// Yes, you use the same NOW() without the quotes.
// Because your datatype is set to DATE it will insert only the date

PHP

$dt = date('Y-m-d');
INSERT INTO t1 (dateposted) VALUES ( '$dt' )
// This will insert date into the col using php var.

TIME (datatype)

MYSQL

INSERT INTO t1 (dateposted) VALUES ( NOW() )
// Yes, you use the same NOW() as well. 
// Because your datatype is set to TIME it will insert only the time

PHP

$dt = date('h:i:s');
INSERT INTO t1 (dateposted) VALUES ( '$dt' )
// This will insert time.

score -1 · Answer 13 · answered Sep 25 '15 at 06:44

-1

I believe, you need to change your code little bit like follows alongside with your database filed type.

mysql_query("INSERT INTO table (`dateposted`) VALUES ($datetime)");

Hope, will work perfectly.

answered Sep 25 '15 at 06:44

tisuchi

129
2
11

score -3 · Answer 14 · edited Oct 03 '17 at 06:59

-3

use this

$date = date('m/d/Y h:i:s', time());

and then in MYSQL do

type=varchar

then date and time will be successfully inserted

edited Oct 03 '17 at 06:59

Matthew Ciaramitaro

1,184
1
13
27

answered Oct 03 '17 at 05:25

vikas pandey

1

your first function is the same as used in the question. And the rest of this answer doesn't warrant reopening a 5 year old question. Please explain why you think your solution is better than those that were posted 5 years ago. – Matthew Ciaramitaro Oct 03 '17 at 05:37
because in old question not explain about data type – vikas pandey Oct 03 '17 at 05:51

score -5 · Answer 15 · edited Sep 08 '16 at 20:24

-5

Try this

$('#datepicker2').datepicker('setDate', new Date('<?=$value->fecha_final?>'));

edited Sep 08 '16 at 20:24

Saranjith

11,242
5
69
122

answered Sep 08 '16 at 19:00

Cristhian Bonilla

1

This will set a jQuery datepicker. The question was about inserting into a MySQL database. – Teepeemm Jul 24 '19 at 17:02

Insert current date in datetime format mySQL

15 Answers15

DATETIME (datatype)

DATE (datatype)

TIME (datatype)

Linked

Related