Categories
Database MySQL

Pulling database values in multiple formats

When we pull a value from MySQL or another database, there's nothing stopping us from pulling the same value in different ways within the same query.

For instance, say we want to pull dates in their raw format, which is suitable for sorting but may not be the most useful formatting for display.  We might find a format like "Jan-13-2009 · Wed" more useful for display purposes, though it's useless for sorting on.

We could massage the data in PHP to alter the date format, but it would be much faster and more efficient to let the database server deal with that.  You'll also have cleaner PHP code if you deliver attributes from your database server ready to use.

Here's an example of a query that pulls all values (the asterisk) followed by additional formats of two dates.  Note that the additional formats are manipulations of data which have already been pulled in their raw format by the asterisk in the select statement.  Note also that I've adopted the convention of  prepending "fmt_" to these derived attributes.

1
2
3
4
5
SELECT
  *,
  DATE_FORMAT(date_received,'%Y-%b-%d · %a') AS fmt_date_received,
  DATE_FORMAT(date_dispatched,'%Y-%b-%d · %a') AS fmt_date_dispatched,
FROM packages

You're now free to use date_received and fmt_date_received independently throughout your code.

Leave a Reply