Turn postgres date representation into ISO 8601 string

postgresql date elixir iso8601 ecto
CallMeNorm picture CallMeNorm · Aug 8, 2016 · Viewed 31.2k times · Source

I'm trying to format a Postgres date representation into a ISO 8601 string. I'm assuming that there is a Postgres function that can do it, but I found the documentation short on examples.

My query is 

SELECT
  now()::timestamp

which returns

[{{2016, 8, 9}, {3, 56, 55, 754181}}]

I'm trying to get the date into a format that looks more like 2016-8-9T03:56:55+00:00.

What changes do I need to make to my query to make that happen? Thanks for your help.

Answer

CallMeNorm picture CallMeNorm · Aug 8, 2016

I think I found a way to do the formatting, but it's not ideal because I'm writing the formatting myself.

Here is a potential solution:

SELECT to_char (now()::timestamp at time zone 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS"Z"')

Related questions

How to compare dates in datetime fields in Postgresql?

I have been facing a strange scenario when comparing dates in postgresql(version 9.2.4 in windows). I have a column in my table say update_date with type 'timestamp without timezone'. Client can search over this field with only date (i.…

Read More
Postgresql query between date ranges

I am trying to query my postgresql db to return results where a date is in certain month and year. In other words I would like all the values for a month-year. The only way i've been able to do …

Read More
PostgreSQL: days/months/years between two dates

I am looking for a way to implement the SQLServer-function datediff in PostgreSQL. That is, This function returns the count (as a signed integer value) of the specified datepart boundaries crossed between the specified startdate and enddate. datediff(dd, '2010…

Read More