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.

• CCates: Dateposted is the datetime format.

Answer 1

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')");

• Jeff_Alieffson: 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.
• Ejaz: @Jeff_Alieffson is right. Using UTC_TIMESTAMP() (dev.mysql.com/doc/refman/5.5/en/…) is preferred instead of NOW()
• user9437856: I tried same but I am getting 0000-00-00 00:00:00 and my datatype is datetime. any help in this?
• DaWe: Would be great to use PDO instead of mysql_query(), wouldn't?

Answer 2

Try this instead

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

Answer 3

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

Answer 4

you can use CURRENT_TIMESTAMP, mysql function

Answer 5

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

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

Answer 6

"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.

• CCates: So I would do date("YYYY/MM/DD HH:MM:SS", time());?
• Jan Hančič: No? use dashes(-) not slashes (/).

Answer 7

 <?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">

• Ajean: 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.

Answer 8

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')" ; 

Answer 9

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

Answer 10

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.

Answer 11

Just use with your timezone :

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

Answer 12

$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);

Answer 13

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.

Answer 14

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

• Matthew Ciaramitaro: 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.
• vikas pandey: because in old question not explain about data type

Answer 15

Try this

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

• Teepeemm: This will set a jQuery datepicker. The question was about inserting into a MySQL database.