[php] Insert current date in datetime format mySQL

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.

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

Try this

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

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

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

Just use with your timezone :

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

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

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

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

Try this instead

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

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

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

you can use CURRENT_TIMESTAMP, mysql function

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.

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

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

DATETIME (datatype)

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

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

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

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

TIME (datatype)

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

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