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

Question

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.

Craig Ringer · Accepted Answer · 2014-10-06 23:21:46Z

2

=> 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.

answered Oct 6, 2014 at 23:21

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

terse Over a year ago

Thanks, the constructors are exactly what I need. I'm still a bit confused as why the example with now() works, it is clearly evaluating that expression.

Craig Ringer Over a year ago

@terse now() is a very weird special case. I really want to remove all references to that from the examples in the documentation, relegating it to the appendix that's all that such a messy historical hack warrants. Essentially PostgreSQL treats the string literal now() as a special case timestamp representation when parsing, implicitly converting it to the current timestamp. That's why it's valid to cast now() to a timestamp. These days you should just use the current_timestamp pseudo-function instead.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related