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
2012/4/3 Chris Angelico <rosuav@gmail.com>
Show quoted text
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.htmlSELECT 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:
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
Regards,
Bartek
2012/4/3 Chris Angelico <rosuav@gmail.com>
Show quoted text
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general