Re: Question on how to use to_timestamp()

On 2/13/16, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
> 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

Oops. I've just discovered that letter.

Adrian, your answer is not fully correct, because
'2016-01-01T00:00:00Z' is *NOT* the same as '2016-01-01 00:00:00-08'!
Unfortunately, "to_timestamp" always returns timestamptz in a
time-zone offset from current "TIME ZONE" setting:

postgres=# SET TIME ZONE 'Europe/London';
SET
postgres=# SELECT ts::timestamptz, to_timestamp(ts,
'YYYY-MM-DD"T"HH24:MI:SSZ') FROM
(VALUES('2016-01-01T00:00:00Z'))t(ts);
           ts           |      to_timestamp
------------------------+------------------------
 2016-01-01 00:00:00+00 | 2016-01-01 00:00:00+00
(1 row)

postgres=# SET TIME ZONE 'Pacific/Honolulu';
SET
postgres=# SELECT ts::timestamptz, to_timestamp(ts,
'YYYY-MM-DD"T"HH24:MI:SSZ') FROM
(VALUES('2016-01-01T00:00:00Z'))t(ts);
           ts           |      to_timestamp
------------------------+------------------------
 2015-12-31 14:00:00-10 | 2016-01-01 00:00:00-10
(1 row)

postgres=# SET TIME ZONE 'Australia/Sydney';
SET
postgres=# SELECT ts::timestamptz, to_timestamp(ts,
'YYYY-MM-DD"T"HH24:MI:SSZ') FROM
(VALUES('2016-01-01T00:00:00Z'))t(ts);
           ts           |      to_timestamp
------------------------+------------------------
 2016-01-01 11:00:00+11 | 2016-01-01 00:00:00+11
(1 row)

... and it can't get time zone from an input string:
postgres=# SELECT ts::timestamptz, to_timestamp(ts,
'YYYY-MM-DD"T"HH24:MI:SSOF') FROM
(VALUES('2016-01-01T00:00:00Z'))t(ts);
ERROR:  "TZ"/"tz"/"OF" format patterns are not supported in to_date

So Deven's query can be rewritten as:

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
     $2 <= (data->>'timestamp')::timestamptz
       AND (data->>'timetsamp')::timestamptz <= $3  -- "<=" or just "<"?
ORDER BY
     (data->>'timestamp')::timestamptz
) AS datapoints
$$
LANGUAGE SQL;

Deven, pay attention "start_time" and "end_time" are "timestamp", not
"timestampTZ", so comparison uses "TIME ZONE" setting:
postgres=# SET TIME ZONE 'Pacific/Honolulu';
SET
postgres=# SELECT ts::timestamp, ts::timestampTZ, ts::timestamp <
ts::timestampTZ FROM (VALUES('2016-01-01T00:00:00Z'))t(ts);
         ts          |           ts           | ?column?
---------------------+------------------------+----------
 2016-01-01 00:00:00 | 2015-12-31 14:00:00-10 | f
(1 row)

postgres=# SET TIME ZONE 'Europe/London';
SET
postgres=# SELECT ts::timestamp, ts::timestampTZ, ts::timestamp <
ts::timestampTZ FROM (VALUES('2016-01-01T00:00:00Z'))t(ts);
         ts          |           ts           | ?column?
---------------------+------------------------+----------
 2016-01-01 00:00:00 | 2016-01-01 00:00:00+00 | f
(1 row)

postgres=# SET TIME ZONE 'Australia/Sydney';
SET
postgres=# SELECT ts::timestamp, ts::timestampTZ, ts::timestamp <
ts::timestampTZ FROM (VALUES('2016-01-01T00:00:00Z'))t(ts);
         ts          |           ts           | ?column?
---------------------+------------------------+----------
 2016-01-01 00:00:00 | 2016-01-01 11:00:00+11 | t
(1 row)

If you want to compare using specific time zone, you have to convert
input values to it:
...
WHERE
     data->>'id'=$1 AND
     ($2 AT TIME ZONE 'America/New_York') <= (data->>'timestamp')::timestamptz
       AND
       (data->>'timetsamp')::timestamptz <= ($3 AT TIME ZONE
'America/New_York')  -- "<=" or just "<"?
ORDER BY
...

> 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.klaver@aklaver.com

--
Best regards,
Vitaly Burovoy