Re: [GENERAL] Question on how to use to_timestamp()

[Vitaly Burovoy]

On 2/13/16, Adrian Klaver  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', '-MM-DDTHH24:MI:SSZ')>=$2 AND
>>  to_timestamp(data->>'timetsamp', '-MM-DDTHH24:MI:SSZ')<=$3
>> ORDER BY
>>  to_timestamp(data->>'timestamp', '-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', '-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',
> '-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,
'-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,
'-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,
'-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,
'-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') <= 

Re: [GENERAL] Question on how to use to_timestamp()

[Vitaly Burovoy]

On 2/13/16, 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', '-MM-DDTHH24:MI:SSZ')>=$2 AND
> to_timestamp(data->>'timetsamp', '-MM-DDTHH24:MI:SSZ')<=$3
> ORDER BY
> to_timestamp(data->>'timestamp', '-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', '-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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Question on how to use to_timestamp()

[Deven Phillips]

Thanks all!
On Feb 13, 2016 11:06 PM, "Tom Lane"  wrote:

> Vitaly Burovoy  writes:
> > On 2/13/16, Deven Phillips  wrote:
> >> I'm trying to convert a series of ISO8601 strings into TIMESTAMPs for
> use
> >> with a function: ...
>
> > If your data is already in a correct ISO8601 format, you can use a
> > direct cast to timestamptz type:
>
> Yeah.  95% of the time, the answer to "how to use to_timestamp()" is
> "don't".  The native input converter for the date/timestamp/timestamptz
> data types is perfectly capable of parsing most common date formats,
> with a lot less muss and fuss than to_timestamp.  At worst you might have
> to give it a hint about DMY vs. MDY field ordering via the DateStyle
> setting.  If your input is YMD order then you don't have to worry about
> that at all.
>
> regards, tom lane
>

Re: [GENERAL] Question on how to use to_timestamp()

[Tom Lane]

Vitaly Burovoy  writes:
> On 2/13/16, Deven Phillips  wrote:
>> I'm trying to convert a series of ISO8601 strings into TIMESTAMPs for use
>> with a function: ...

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

Yeah.  95% of the time, the answer to "how to use to_timestamp()" is
"don't".  The native input converter for the date/timestamp/timestamptz
data types is perfectly capable of parsing most common date formats,
with a lot less muss and fuss than to_timestamp.  At worst you might have
to give it a hint about DMY vs. MDY field ordering via the DateStyle
setting.  If your input is YMD order then you don't have to worry about
that at all.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Question on how to use to_timestamp()

[Adrian Klaver]

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', '-MM-DDTHH24:MI:SSZ')>=$2 AND
>  to_timestamp(data->>'timetsamp', '-MM-DDTHH24:MI:SSZ')<=$3
> ORDER BY
>  to_timestamp(data->>'timestamp', '-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', '-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', '-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 "', the  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.kla...@aklaver.com


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general