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?