Preamble

Formatting of data should only occur in the final steps of output. Until that point, and as a rule, internally data should remain in a base format that can easily be converted into many others – without being converted into another more basic format first.

For example Unix timestamp for dates or a floating point number for money. Both can readily be converted into more expressive formats without writing code to first parse or disassemble the initial format.

However in a situation where the flow is very specific and unlikely to ever be used to generate a different output it is permissible, even desirable, to generate data in the format it will be finally outputted.

PHP vs. the database

It is assumed that it is faster to get the database to perform such formatting and return requested the data pre-formatted than to loop through the data and perform the formatting in PHP script. As such we need to use SQL to tell PostgreSQL how to format the data.

SELECT to_char(NOW(), 'YYYY-MM-DD"T"HH24:MI:SSZ') as the_time;

Will produce something like:

       the_time       
----------------------
 2013-10-29T21:06:39Z
(1 row)

NB

to_char(timestamp, text) is the function that formats a date / timestamp into text. The first parameter is the timestamp to be formatted, and the second string is the format to use. The timestamp can easily be timestamp column.

You can find out more about the function on this page Data Type Formatting Functions. More info on the string format a little further down the same page, here: Template Patterns for Date/Time Formatting.

The ” (double quotes) around the T escape it. Without them the final output would have been 2013-10-29THH24:25:15Z as T is reserved character in the formatting language.

Lastly the as the_time can be omitted, in which case the resulting column will be named to_char.