Re: Question on how to use to_timestamp()

On 02/13/2016 07:42 PM, Deven Phillips 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');
>
> 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?

test=> SELECT to_timestamp('2016-01-01T00:00:00Z', 'YYYY-MM-DD"T"HH24:MI:SSZ');

to_timestamp
------------------------
2016-01-01 00:00:00-08

http://www.postgresql.org/docs/9.5/interactive/functions-formatting.html
"Ordinary text is allowed in to_char templates and will be output literally. You can put a substring in double quotes to force it to be interpreted as literal text even if it contains pattern key words. For example, in '"Hello Year "YYYY', the YYYY will be replaced by the year data, but the single Y in Year will not be. In to_date, to_number, and to_timestamp, double-quoted strings skip the number of input characters contained in the string, e.g. "XX" skips two input characters."

>
> Thanks in advance!!!
>
> Deven

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com