Cannot output datetime values from table correctly.

[webmaster1]

I'm outputting the minimum datetime as follows:

 

$querymin = "SELECT MIN(datetime) FROM table where something='$something'"; //query
$resultmin = mysql_query($querymin) or die(mysql_error()); //execute
while($rowmin = mysql_fetch_array($resultmin)){ //loop
$min= $rowmin['MIN(datetime)']; //as variable
//echo $min; 
$min= date("m/d/y g:i (A)", $min); //format date
echo $min;

 

2010-04-18 00:00:53, as stored in my database should be formatted like 18/04/10 00:00 (AM) except it formats as: 01/01/70 1:33 (AM) Notice it's about 4 decades out. 

 

I've wasted a fair amount of time trouble-shooting this though I keep on bouncing back and forth between unix timestamps and the strftime() function.

 

Can someone please assist? Why is my formatting messing up my date?

[webmaster1]

Would making my datetime column into a timestamp make life easier?

 

I was avoiding it because I'm cautious of updating the date when I'm not supposed to.

[oni-kun]

Would making my datetime column into a timestamp make life easier?

 

I was avoiding it because I'm cautious of updating the date when I'm not supposed to.

 

Yes. Note 40 years back was the Unix epoch, There's obviously a flaw in your logical calculations.

[PFMaBiSmAd]

Just use the mysql DATE_FORMAT() function in your query, that's what it is for. I would also use an alias name so that you can simply reference the results by the alias -

 

$min= $rowmin['alias_name_you_choose'];

[webmaster1]

By chance, the following works:

 

$querymin = "SELECT MIN(datetime) FROM table where something='$something'"; 
$resultmin = mysql_query($querymin) or die(mysql_error());

while($rowmin = mysql_fetch_array($resultmin)){
$min= $rowmin['MIN(datetime)'];

$min = strtotime($min);
print date('Y-m-d \a\t H:i', $min)."\n";

 

The strtotime() function through me off a little. A multitude of the examples I encountered used the current time for calculation which I had assumed wouldn't be interchangeable with a variable.

[webmaster1]

Just use the mysql DATE_FORMAT() function in your query, that's what it is for. I would also use an alias name so that you can simply reference the results by the alias -

 

$min= $rowmin['alias_name_you_choose'];

 

Trying this now, cheers.

[joel24]

the date function requires a timestamp, not a m/d/y or datetime date.

 

create the timestamp using strtotime, but the mysql date format isn't compatible with that function, you can use mysql to get the datetime as a unix timestamp, i.e. "SELECT MIN(UNIX_TIMESTAMP(datetime)) FROM table where something='$something'"; //query

[webmaster1]

@PFMaBiSmAd / joel24: Thanks for breaking that down. I'm now up to speed with the query and strtotime approach. Cheers!

[PFMaBiSmAd]

but the mysql date format isn't compatible with that function

 

The msyql date format is compatible with the strtotime() function, as demonstrated in the code in Reply #4 in this thread.

[webmaster1]

The msyql date format is compatible with the strtotime() function, as demonstrated in the code in Reply #4 in this thread.

 

Concurred.