Format datetime to use with mysql

[Stefano Mtangoo]

I need to format PHP to use with one of my date field (DATETIME).
I want to get current time and format it. I have checked some googled articles but I cannot get far.
Please help me!

In php use:

$mydate = date('Y-m-d H:i:s');
//this will produce a date like 2009-09-20 17:44:09

$query = mysql_query("INSERT INTO mytable SET mydatefield = '{$mydate}', field2 = 'value2'");

[almostbob]

Dont format the date time in the database
text formatted dates are for humans to read, sql is not human does not need any of that and it makes the processing slower
sql Date, php date, unix timestamp, are all a single 10byte numeric that stores complete date and time
formatting is done for human readability on output
example <?php echo date('DMY h:m',$timestamp} ?> selecting a timestamp renage from num1 to num2 is much faster - less processing than searching text dates for dates between x and 1
sql now() will input the date and time of the update as the update is processing
php date() is now

1253454900 (10bytes)
or
September 20 2009, 1:55pm (25bytes)

not much on 1 row, very much on a million rows

select * from table where date > 1234567890 and date < 1234568890
is much faster than precessing text dates
processing script languages have strtotime() (or an identical function) built in, you dont even have to do any text to time conversions on range select fields

text date time: not good

I'd agree with AB about now() in SQL - better than using date() first and then using it, but if you need the current date for output to screen as well as input to db, you may as well.

[Stefano Mtangoo]

Dont format the date time in the database
text formatted dates are for humans to read, sql is not human does not need any of that and it makes the processing slower
sql Date, php date, unix timestamp, are all a single 10byte numeric that stores complete date and time
formatting is done for human readability on output
example <?php echo date('DMY h:m',$timestamp} ?> selecting a timestamp renage from num1 to num2 is much faster - less processing than searching text dates for dates between x and 1
sql now() will input the date and time of the update as the update is processing
php date() is now

1253454900 (10bytes)
or
September 20 2009, 1:55pm (25bytes)

not much on 1 row, very much on a million rows

select * from table where date > 1234567890 and date < 1234568890
is much faster than precessing text dates
processing script languages have strtotime() (or an identical function) built in, you dont even have to do any text to time conversions on range select fields

text date time: not good

Thanks for explanations. Can you please give example of how to use it?

[hemgoyal_1990]

For Complete Date & time Statement in PHP with MySQL Please Go to:
http://in2.php.net/manual/en/function.date.php

The above will give u all your problem solution.
Thank You..

[Stefano Mtangoo]

For Complete Date & time Statement in PHP with MySQL Please Go to:
http://in2.php.net/manual/en/function.date.php

The above will give u all your problem solution.
Thank You..

Thanks, I'll check!

[hemgoyal_1990]

Thanks, I'll check!

Welcome Dude..

[Stefano Mtangoo]

I solved it with gmdate()

function getnow(){
	//mysql date format -- 'YYYY-MM-DD HH:MM:SS'
	// Create the UNIX Timestamp, using the current system time
	$ptime = time();
	// Convert that UNIX Timestamp into a string (GMT), safe for MySql -- GMT +3
	$toffset = +3.00;
	$stime = gmdate("Y-m-d H:i:s", $ptime+$toffset * 3600);
	return $stime ;
	}

[almostbob]

No dude(dudess?)
store the 10 digit timestamp in the database
convert to text on output
Mysql does not care what it looks like and it is faster smaller and simpler for mysql to process the numeric to search for records between 1 January and 1 March
the returned $time function is much larger than 10bytes
that function would be better in the script used output data to be human readable in reports
Store $ptime
$ptime 1234568888
$stime Monday 13 Feb 2009 10:55am

[Stefano Mtangoo]

No dude(dudess?)
store the 10 digit timestamp in the database
convert to text on output
Mysql does not care what it looks like and it is faster smaller and simpler for mysql to process the numeric to search for records between 1 January and 1 March
the returned $time function is much larger than 10bytes
that function would be better in the script used output data to be human readable in reports
Store $ptime
$ptime 1234568888
$stime Monday 13 Feb 2009 10:55am

So what is your suggestion? I mean can you show me with a code example? That will be of much help.
Thanks for reply also!

[almostbob]

<?php /*input */ 
$ptime=time();
$insert = msql_query("insert into table ($column1,$column2,$column3,$column4,$ptime)");
if (!$insert) {  die("query failed: " . msql_error()); }
/* ouput */
$startdate = strtotime($startdate);
$enddate = strtotime($enddate);
$report = msql_query("select from table * where ptime > %s and ptime < %s, $startdate, $enddate");
if (!$report) {  die("query failed: " . msql_error()); }
while($row = mysql_fetch_array($ouput)) {
echo "$row['column1']  $row['column2'] $row['column3'] $row['column4'] ".date('D M Y H:m',$row['ptime'])."<br />";  }
?>

text date/times in the report generating form are set to timestamp in report processing
this sql select from table * where ptime > %s and ptime < %s, $startdate, $enddate is very much less processor intensive than working on a text string, it just looks for a number greater than X and less than Y

[Stefano Mtangoo]

Thanks,
let me work on that and will feedback on what I have gone so far!