How can I use PHP to display a MYSQL datetime value without displaying the seconds?

November 28, 2006 7:24 AM

How can I strip the seconds off when displaying a MYSQL datetime variable, using only PHP commands, not MYSQL commands like "DATE_FORMAT"?

I have a form where users enter a few dates and times. I don't care about seconds, just the year, month, day, hours, minutes. I'm using PHP with MYSQL, and the MYSQL values are in datetime format.

When I display the contents of those variables, it shows them as, for example:

2006/11/28 17:26:00

What I would like displayed is:

2006/11/28 17:26

I know this can be done with the DATE_FORMAT command in a MYSQL query, but the problem is that I have quite a few queries, all in the form of "SELECT * FROM table WHERE blahblahblah" (which gathers lots of other information besides datetimes), and if I make separate queries in the form "SELECT DATE_FORMAT (startTime, blahblahblah) AS startTime" for all the time values I call up, I'm going to be making my code much longer and harder for other people (who, like myself, are non-programmers with limited PHP knowledge) to work on.

Is there any way that's relatively clean/simple on the PHP end to take, for example, a variable called $startTime, with a value like "2006-11-11 12:13:00", and display it on a page as "2006-11-11 12:13"?

Yes, I have Googled like a motherfucker, but to no avail.

posted by Bugbread to computers & internet (14 comments total)

To round down to the nearest minute, convert to a unix timestamp and divide by 60.

Also, investigate the date() function, eg

date ("H:m:00", $unix_timestamp);
posted by Leon at 7:30 AM on November 28, 2006

BTW, "SELECT *" is considered bad practice. You can also do:

SELECT *, DATE_FORMAT(blah) AS parsedDate
posted by Leon at 7:32 AM on November 28, 2006

$startTime = substr($startTime, 0, strlen($startTime)-3);
posted by Blazecock Pileon at 7:32 AM on November 28, 2006 [1 favorite]

And converting to a unix timestamp can be done with strtotime, like so:

date("Y/m/d H:m", strtotime($your_mysql_datevar));
posted by milov at 7:33 AM on November 28, 2006

You can use strtotime() to convert to unix timestamp, then date() to format it.

this should work:

date("Y:M:d H:m", strtotime($startTime))
posted by MetaMonkey at 7:37 AM on November 28, 2006

Oops, I'm late and made a small mistake, more like:

date("Y:m:d H:i", strtotime($startTime))
posted by MetaMonkey at 7:40 AM on November 28, 2006

If you are doing this often, I would suggest you use a substring solution to just cut off the last three characters of the string rather than using strtotime(). While it is a great and useful function, it is extremely slow, so if this is something that will be used a lot, you will have much better results with a simple string manipulation.
posted by chrisroberts at 8:09 AM on November 28, 2006

Wow, everyone made this way too complicated.

Blazecock Pileon's way is the way I'd do it.
posted by jesirose at 8:10 AM on November 28, 2006

Good answers, all. It's working just as I wanted, now. Cheers!
posted by Bugbread at 8:11 AM on November 28, 2006

You could also create a view as such:

CREATE VIEW blahblahblah_nosec AS SELECT col1, col2, DATE_FORMAT('myformat') AS col3, col4, ... FROM blahblahblah

Then just select from this view whenever you want the date formatted.
posted by sbutler at 8:14 AM on November 28, 2006

The load is very low (this is a dynamic page thingie only used by max of 3 people at a time), so I went with milov's answer just because I'm more familiar with it than with the substr command, but if I ever deploy something that sees more intensive use (I doubt it, as I'm not a programmer, but you never know), I'll keep the strtotime() latency issue in mind and use Blazecock's substr command.
posted by Bugbread at 8:14 AM on November 28, 2006

Shame on me, I also used "m" for minutes instead of "i", even though I must have used date() and strtotime() together like this a thousand times now.

And just for the hell of it, here is a regular expression version:
$startTime = preg_replace("'\:[0-9]{2}$'", ', $startTime);
posted by milov at 8:53 AM on November 28, 2006

oops, I lost a quote on preview, there should be two single quotes in that second parameter
posted by milov at 9:15 AM on November 28, 2006

if I ever deploy something that sees more intensive use

There's also mysql's SELECT UNIX_TIMESTAMP(startTime) for future consideration. This is what I usually do, then use php's strftime() to do the formatting. (Actually, I usually just store the time as a unix timestamp in the database (varchar(10) datatype). It just makes things easier to deal with on the php side, the tradeoff being not having a human-readable value in the db.)


making my code much longer and harder for other people to work on

BTW, "SELECT *" is considered bad practice.

Yeah, selecting only the fields you are really going to use would probably make it easier for other people to read later since they won't have to look up the database schema to know what data they have "in hand" (among other considerations).
posted by and hosted from Uranus at 10:26 AM on November 28, 2006