Issue displaying time in the proper order and format 1

[Evil8]

I have an SQL database of events with date and time. Time ("wTime") was stored as 10:00:00 or 01:00:00. 10:00 should be 10:00 AM and 1:00 should be 1:00 PM The SQL querry is ...ORDER BY wDate, wTime";... output is tabled -

<td><?php echo date('H:i, strtotime($row['wTime']))?></td>

Now the issue is that 1:00 is showing up before 10:00:00 and I'm not sure how or where to change it. I first changed the php to ... echo date('H:i A, stringtotime(... but that changed ALL the times to AM. That didn't suprise me so I went back to the table and changed 1:00:00 to 13:00:00 and now it sorts in the right order, but users aren't going to know 13:00 PM is 1:00 PM. I think the solution is in the php for formatting the display.

Any help as always is greatly appreciated!

Thanks,

Evil8

 

[jpadie]

if it's just display then do this

date('H:i A', strtotime($row['wTime'])); //appends AM/PM

don't forget to set the default timezone first.

date_default_timezone_set('UTC'); //or whatever

if the problem is in the sorting then change the order by clause of the sql to

ORDER BY CAST(wDate AS DATE) ASC, CAST(wTime AS TIME) ASC;

this should only be necessary if you have not properly stipulated (within the sql create statement) that those columns should be for date and time values.

this assumes you are using mysql or a rdbms that supports time/date type fields and the CAST function.

 

[Evil8]

Thanks jpadie! I think this will work, but I'm not sure where to add line for the default timezone.

<?php

   while($row = mysql_fetch_array($result))
   }

       ?>

<tr>
   <td><?php echo date('m/d/Y', strtotime($row['wDate']))?></td>
   <td><?php echo date('H:i A', strtotime($row['wTime']))?></td>
   ...
</tr>

Thanks!

 

[Evil8]

I think I got it running correctly now.

I changed the output to:

<td><?php echo date('g:i A' strtotime($row['wTime]))?></td>

I guess I don't need to set the time zone default...

Thanks!

 

[jpadie]

the g shows the 12hr clock without leading zeros. other options exist.
you need to set the default time zone somewhere. it can be in your php.INI or ij code. if you do it in code then the line should be before you call any other date related function. if you do not then php will fire an error (warning) each time a date function is used. you wont see these if the error display is turned off but php still fires the error and writes the log entry (probably) and thus you waste processor cycles

 

[Evil8]

hummm... okay I've looked and I do have a default timezone listed in the php.ini file, America/Los_Angeles. I'll have to change that to America/Chicago. So that's probably great advice there. Ahhh now that's done. Thanks for that!

As far as the web page display goes it looks to be working as the client wants. Times are displaying as 10:00 AM or 1:00 PM and in the correct sort order.