epoch to timestamp

On Fri, May 09, 2003 at 18:05:45 -0300,
Lucas Lain <lainl@aconectarse.com> wrote:

can anyone tell me how to convert an epoch date to timestamp format??

If the epoch date is an integer with seconds since the unix epoch,
you can cast it to abstime and then to timestamp.

i really dont know how to do it ... i'm a newbie
it is something like this?

select cast(105471234 AS abstime) ...

and then?

On Fri, 9 May 2003 16:36:48 -0500
Bruno Wolff III <bruno@wolff.to> wrote:

On Fri, May 09, 2003 at 18:05:45 -0300,
Lucas Lain <lainl@aconectarse.com> wrote:

can anyone tell me how to convert an epoch date to timestamp format??

If the epoch date is an integer with seconds since the unix epoch,
you can cast it to abstime and then to timestamp.

--
Lucas Lain
lainl@aconectarse.com

Hi Lucas,

Just nest or chain your casts as appropriate.

example=> select cast(cast(105471234 as abstime) as timestamp);
timestamp
---------------------
1973-05-05 13:33:54
(1 row)

Time: 1.81 ms
example=> select 105471234::abstime::timestamp;
timestamp
---------------------
1973-05-05 13:33:54
(1 row)

Time: 1.97 ms

Best,

Randall

On Monday, May 12, 2003, at 11:05 AM, Lucas Lain wrote:

--On Monday, May 12, 2003 18:30:11 -0200 Achilleus Mantzios
<achill@matrix.gatewaynet.com> wrote:

On Mon, 12 May 2003, Lucas Lain wrote:

i really dont know how to do it ... i'm a newbie
it is something like this?

select cast(105471234 AS abstime) ...

and then?

Just do
select 105471234::abstime::timestamp;

Along the same lines, how can I get from seconds to hour/minute/seconds?

(an interval?)

--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

thank everybody ... thank you very much!
works great!

On Mon, 12 May 2003 18:30:11 -0200 (GMT+2)
Achilleus Mantzios <achill@matrix.gatewaynet.com> wrote:

On Mon, 12 May 2003, Lucas Lain wrote:

i really dont know how to do it ... i'm a newbie
it is something like this?

select cast(105471234 AS abstime) ...

and then?

Just do
select 105471234::abstime::timestamp;

On Fri, 9 May 2003 16:36:48 -0500
Bruno Wolff III <bruno@wolff.to> wrote:

On Fri, May 09, 2003 at 18:05:45 -0300,
Lucas Lain <lainl@aconectarse.com> wrote:

can anyone tell me how to convert an epoch date to timestamp format??

If the epoch date is an integer with seconds since the unix epoch,
you can cast it to abstime and then to timestamp.

--
==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel: +30-210-8981112
fax: +30-210-8981877
email: achill@matrix.gatewaynet.com
mantzios@softlab.ece.ntua.gr

--
Lucas Lain
lainl@aconectarse.com

--On Monday, May 12, 2003 18:39:59 -0200 Achilleus Mantzios
<achill@matrix.gatewaynet.com> wrote:

On Mon, 12 May 2003, Larry Rosenman wrote:

As an example see:

select date_part('minute',(105471234::abstime::timestamp) + ('10
years')::interval);

I actually have just seconds (from my LD carrier), and want to store it in
hours/minutes/seconds.

Along the same lines, how can I get from seconds to hour/minute/seconds?

(an interval?)

--
==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel: +30-210-8981112
fax: +30-210-8981877
email: achill@matrix.gatewaynet.com
mantzios@softlab.ece.ntua.gr

--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

Larry,

I actually have just seconds (from my LD carrier), and want to store it in
hours/minutes/seconds.

If you store it as an interval, you will end up with:

staffos=# select '12742329 seconds'::INTERVAL;
interval
-------------------
147 days 11:32:09

In fact, you can't avoid interval conversion to days, hours, minutes.

--
Josh Berkus
Aglio Database Solutions
San Francisco

--On Monday, May 12, 2003 09:00:11 -0700 Josh Berkus <josh@agliodbs.com>
wrote:

Larry,

I actually have just seconds (from my LD carrier), and want to store it
in hours/minutes/seconds.

If you store it as an interval, you will end up with:

staffos=# select '12742329 seconds'::INTERVAL;
interval
-------------------
147 days 11:32:09

In fact, you can't avoid interval conversion to days, hours, minutes.

Yeah, I remembered that after I hit send (so, what else is new? /me
looking like
a dummy :-) )

--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

I wanted an interval to be displayed as HH:MI:SS even when the
number of hours is greater than 24. I resorted to something like
this:

acctsessiontime is an interval.

SELECT
date_part('seconds', acctsessiontime) as connectseconds,
date_part('minutes, acctsessiontime) as connectminutes,
date_part('hours', acctsessiontime) as connecthours,
date_part('days', acctsessiontime) as connectdays
....

I then did the old connecthours += connectdays * 24 routine.

Is there some sort of inverse "date_trunc" that would enable me
to say: to_char(acctsessiontime, 'HH:MI:SS') and get, for example,
147:23:12?

--
Chris Linstruth <cjl@qnet.com>
QNET
1529 East Palmdale Blvd Suite 200
Palmdale, CA 93550
(661) 538-2028

On Mon, 12 May 2003, Larry Rosenman wrote:

On Mon, 12 May 2003, Lucas Lain wrote:

i really dont know how to do it ... i'm a newbie
it is something like this?

select cast(105471234 AS abstime) ...

and then?

Just do
select 105471234::abstime::timestamp;

On Fri, 9 May 2003 16:36:48 -0500
Bruno Wolff III <bruno@wolff.to> wrote:

On Fri, May 09, 2003 at 18:05:45 -0300,
Lucas Lain <lainl@aconectarse.com> wrote:

can anyone tell me how to convert an epoch date to timestamp format??

If the epoch date is an integer with seconds since the unix epoch,
you can cast it to abstime and then to timestamp.

--
==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel: +30-210-8981112
fax: +30-210-8981877
email: achill@matrix.gatewaynet.com
mantzios@softlab.ece.ntua.gr

On Mon, 12 May 2003, Larry Rosenman wrote:

As an example see:

select date_part('minute',(105471234::abstime::timestamp) + ('10
years')::interval);

Along the same lines, how can I get from seconds to hour/minute/seconds?

(an interval?)

--
==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel: +30-210-8981112
fax: +30-210-8981877
email: achill@matrix.gatewaynet.com
mantzios@softlab.ece.ntua.gr

I would like to create a new column type that just holds a text string
(a lisp form). My only reason for creating a type is so that I can
distinguish this field type from text fields and perform some extended
processing when they are read by the client.

Below is my first (newbie) attempt. Do I really have to do write
something in C to accomplish this? Is there any kind of type "alias"
feature?

Thanks,

John DeSoi, Ph.D.

===
test=# create function lisp_text (text) returns text as 'select $1;'
language sql;
CREATE FUNCTION
test=# select lisp_text('test 1234');
lisp_text
-----------
test 1234
(1 row)

test=# create type lisp (input = lisp_text, output = lisp_text,
internallength = variable, default = 'nil', delimiter = ' ');
ERROR: TypeCreate: function lisp_text(cstring) does not exist
test=# create function lisp_text (cstring) returns text as 'select $1;'
language sql;
ERROR: SQL functions cannot have arguments of type cstring

On Mon, 12 May 2003, John DeSoi wrote:

I would like to create a new column type that just holds a text string
(a lisp form). My only reason for creating a type is so that I can
distinguish this field type from text fields and perform some extended
processing when they are read by the client.

Below is my first (newbie) attempt. Do I really have to do write
something in C to accomplish this? Is there any kind of type "alias"
feature?

You might just want to make a domain, something like:
create domain lisp as text;