Re: Question on how to use to_timestamp()

On 2/13/16, Deven Phillips <deven(dot)phillips(at)gmail(dot)com> wrote:
> I'm trying to convert a series of ISO8601 strings into TIMESTAMPs for use
> with a function:
>
> CREATE OR REPLACE FUNCTION v1_nexus_vlan_count(id TEXT, start_time
> TIMESTAMP, end_time TIMESTAMP)
> RETURNS TEXT AS $$
> SELECT jsonb_pretty(jsonb_agg(row_to_json(datapoints))) AS data_array FROM
> (
> SELECT
> data->>'timestamp' AS collection_time,
> data->'data'->'vlans'->>'available' AS available,
> data->'data'->'vlans'->>'total' AS total,
> data->'data'->'vlans'->>'used' AS used
> FROM
> gathered_data
> WHERE
> data->>'id'=$1 AND
> to_timestamp(data->>'timestamp', 'YYYY-MM-DDTHH24:MI:SSZ')>=$2 AND
> to_timestamp(data->>'timetsamp', 'YYYY-MM-DDTHH24:MI:SSZ')<=$3
> ORDER BY
> to_timestamp(data->>'timestamp', 'YYYY-MM-DDTHH24:MI:SSZ')) AS
> datapoints $$
> LANGUAGE SQL;
>
> The conversions for to_timestamp() seems to be my problem. I keep getting
> an error:
>
> # SELECT to_timestamp('2016-01-01T00:00:00Z', 'YYYY-MM-DDTHH24:MI:SSZ');

If your data is already in a correct ISO8601 format, you can use a
direct cast to timestamptz type:

# SELECT '2016-01-01T00:00:00Z'::timestamptz;
timestamptz
------------------------
2016-01-01 00:00:00+00
(1 row)

"to_timestamp" is used for some complex cases:
> to_timestamp and to_date exist to handle input formats
> that cannot be converted by simple casting.

> ERROR: invalid value ":0" for "MI"
> DETAIL: Value must be an integer.
> Time: 1.016 ms
>
> Could anyone suggest what it is that I might be doing wrong here?
>
> Thanks in advance!!!
> Deven
>

[1] http://www.postgresql.org/docs/devel/static/functions-formatting.html
--
Best regards,
Vitaly Burovoy