CODEWITHSUNDEEP
phpmysql
CCates
CCates

Reputation: 1255

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.

Upvotes: 114

Views: 504925

Answers (15)

Dexter
Dexter

Reputation: 9344

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.

Upvotes: 1

Rehan
Rehan

Reputation: 11

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

Upvotes: 0

vikas pandey
vikas pandey

Reputation: 1

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

Upvotes: -3

user8123187
user8123187

Reputation:

Just use with your timezone : 

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

Upvotes: 0

Prakash GPz
Prakash GPz

Reputation: 1523

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

Upvotes: 1

Cristhian Bonilla
Cristhian Bonilla

Reputation: 1

Try this

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

Upvotes: -5

Salem
Salem

Reputation: 784

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

Upvotes: 1

tisuchi
tisuchi

Reputation: 129

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.

Upvotes: -1

PratapSingh hajari
PratapSingh hajari

Reputation: 11

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

Upvotes: 1

Abdullah
Abdullah

Reputation: 538

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

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

Upvotes: 6

Gaurang
Gaurang

Reputation: 1958

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

Upvotes: 9

Diar Selimi
Diar Selimi

Reputation: 71

you can use CURRENT_TIMESTAMP, mysql function

Upvotes: 7

Aaron W.
Aaron W.

Reputation: 9299

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

Upvotes: 257

Erik Giberti
Erik Giberti

Reputation: 1235

Try this instead

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

Upvotes: 39

Jan Hančič
Jan Hančič

Reputation: 53929

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

Upvotes: 2

Related Questions