CODEWITHSUNDEEP
postgresql
terse
terse

Reputation: 346

How to use Unix epoch time values with Postgres tsrange and tstzrange types?

Is it possible to insert Unix epoch time values into a field of type tstzrange/tsrange without converting to some other time string format first?

Using Postgres 9.3, an invalid input syntax error is produced when tstzrange/tsrange are supplied with to_timestamp() values:

=> select '["to_timestamp(1267253400.069539)",]'::tstzrange;
ERROR:  invalid input syntax for type timestamp with time zone: "to_timestamp(1267253400.069539)"
LINE 1: select '["to_timestamp(1267253400.069539)",]'::tstzrange;

which is a bit confusing given that to_timestamp() returns a value with type timestamp with time zone.

It is not as simple as functions not being allowed for fields of these types because the now() function works fine:

=> select '["now()",]'::tstzrange;
               tstzrange
---------------------------------------
["06-OCT-14 15:11:05.488949 -07:00",)
(1 row)

The solution I'm using now is to convert the epoch times to formatted strings (e.g. '2010-02-27T06:50:00.069539-00'), which work fine with tstzrange/tsrange. I presume this is not as efficient as supplying epoch times directly and would appreciate knowing if it is possible.

Upvotes: 0

Views: 1481

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324781

=> select '["to_timestamp(1267253400.069539)",]'::tstzrange;

That's not supplying a tstzrange with to_timestamp output.

It's trying to use the text string "to_timestamp(1267253400.069539)" as the literal value of the first half of a timestamp range.

You'll want to use the convenient function form constructor:

select tstzrange( to_timestamp(1267253400.069539),null );

... as that way any contained expressions get evaluated.

Upvotes: 2

Related Questions