From: | Deven Phillips <deven(dot)phillips(at)gmail(dot)com> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Question on how to use to_timestamp() |
Date: | 2016-02-14 03:42:29 |
Message-ID: | CAJw+4NCQuvN0=MTgAvtruwhbN=Pjk3sfPffmqTAJ2hmLGfeEsw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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?
Thanks in advance!!!
Deven
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2016-02-14 03:50:47 | Re: Question on how to use to_timestamp() |
Previous Message | Yury Zhuravlev | 2016-02-13 19:19:39 | Re: Test CMake build |