Lists: | pgsql-general |
---|
From: | Gregory Seidman <gss+pg(at)cs(dot)brown(dot)edu> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | reverse of extract epoch? |
Date: | 2002-06-03 01:17:50 |
Message-ID: | 20020603011750.GA12152@jamaica.cs.brown.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
I happen to like using epoch-based timestamps. Given that, I make heavy use
of the following function as a default value in several places:
CREATE FUNCTION EPOCHNOW () RETURNS int
AS 'SELECT (EXTRACT (epoch FROM NOW()))::int'
LANGUAGE SQL;
Now I want to create its reverse so I can get a timestamp (with tz) from an
epoch-based integer. Is there some simple way of doing it? Right now I have
in place the following:
CREATE FUNCTION EPOCHtoTS (int) RETURNS timestamp
AS 'SELECT NOW() + (($1 - (EXTRACT (epoch FROM NOW()))::int) || \' seconds\')::interval'
LANGUAGE SQL;
It has to be easier than that, right? Realistically, if I can convert
from epoch to timestamp simply then I will move over to having things
stored in the db as timestamps and only use epoch-based times
programmatically.
--Greg
From: | Neil Conway <nconway(at)klamath(dot)dyndns(dot)org> |
---|---|
To: | gss+pg(at)cs(dot)brown(dot)edu |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: reverse of extract epoch? |
Date: | 2002-06-03 13:39:57 |
Message-ID: | 20020603093957.5db5e5c1.nconway@klamath.dyndns.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On Sun, 2 Jun 2002 21:17:50 -0400
"Gregory Seidman" <gss+pg(at)cs(dot)brown(dot)edu> wrote:
> Now I want to create its reverse so I can get a timestamp (with tz) from an
> epoch-based integer. Is there some simple way of doing it?
Try:
select "timestamp"(extract(epoch from now())::int);
Cheers,
Neil
--
Neil Conway <neilconway(at)rogers(dot)com>
PGP Key ID: DB3C29FC
From: | Gregory Seidman <gss+pg(at)cs(dot)brown(dot)edu> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: reverse of extract epoch? |
Date: | 2002-06-03 18:32:05 |
Message-ID: | 20020603183205.GA14198@jamaica.cs.brown.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Neil Conway sez:
} On Sun, 2 Jun 2002 21:17:50 -0400
} "Gregory Seidman" <gss+pg(at)cs(dot)brown(dot)edu> wrote:
} > Now I want to create its reverse so I can get a timestamp (with tz) from an
} > epoch-based integer. Is there some simple way of doing it?
}
} Try:
}
} select "timestamp"(extract(epoch from now())::int);
Yowza! It's been there all along! I actually wanted timestamptz(), as it
turns out, but I found that easily. Thank you!
} Cheers,
}
} Neil
--Greg