Thread: Cast timestamptz to/from integer?
I work a lot with Unix times as integers, but would like to store them in Postgres as 'timestamp(0) with time zone' for convenience and readability. Unfortunately the syntax to translate between the two is a little cumbersome, so I'm looking at hiding it away behind a function - or a cast. However, the CREATE CAST docs say that I have to be the owner of either the source or target types, which presumably means I can't define a cast from timestamptz to int. Is there a workaround for this? Chris Angelico
I think You can use epoch
there is an example: http://www.postgresql.org/docs/9.1/static/functions-datetime.html
SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 982384720.12 * INTERVAL '1 second';
Regards,
Bartek
Bartek
2012/4/3 Chris Angelico <rosuav@gmail.com>
I work a lot with Unix times as integers, but would like to store them
in Postgres as 'timestamp(0) with time zone' for convenience and
readability. Unfortunately the syntax to translate between the two is
a little cumbersome, so I'm looking at hiding it away behind a
function - or a cast. However, the CREATE CAST docs say that I have to
be the owner of either the source or target types, which presumably
means I can't define a cast from timestamptz to int.
Is there a workaround for this?
Chris Angelico
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Tue, Apr 3, 2012 at 7:11 PM, Bartosz Dmytrak <bdmytrak@gmail.com> wrote: > I think You can use epoch > there is an > example: http://www.postgresql.org/docs/9.1/static/functions-datetime.html > > > SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 982384720.12 * INTERVAL '1 > second'; Yep, but when you do that a lot, your statement gets extremely long. I can create a function that'll hide the mess away, but what I'm hoping to do is simply cast: SELECT 982384720::timestamptz; ChrisA
There is a build in function which encapsulates that statement:
Regards,
Bartek
SELECT to_timestamp (982384720);
EXPLAIN ANALYZE shows:
Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.002..0.003 rows=1 loops=1)
so this looks cheap
Bartek
2012/4/3 Chris Angelico <rosuav@gmail.com>
On Tue, Apr 3, 2012 at 7:11 PM, Bartosz Dmytrak <bdmytrak@gmail.com> wrote:Yep, but when you do that a lot, your statement gets extremely long. I
> I think You can use epoch
> there is an
> example: http://www.postgresql.org/docs/9.1/static/functions-datetime.html
>
>
> SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 982384720.12 * INTERVAL '1
> second';
can create a function that'll hide the mess away, but what I'm hoping
to do is simply cast:
SELECT 982384720::timestamptz;
ChrisA
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general