Thread: How to display a unixtimestamp from a timestamp record?

Hello!again i was faced to a problem i was trying to do create view in wich i
had the next fields:

CREATE TABLE "tbacct" ("user_name" character varying(32),"nas_identifier" character varying(15),"nas_port"
int4,"acct_session_id"character varying(15),"caller_id" character varying(16),"called_station_id" character
varying(16),"framed_ip_address"character varying(15),"acct_disconnect_cause" character varying(40),"acct_session_time"
int4,"acct_timestamp"timestamp,"realm" character varying(64)
 
);

and i need view in wich acct_timestamp is a int8

USERNAME
IP-ADDRESS
74
000B2E5B
NULL
6572
IP-ADDRESS
NULL
4
2000-11-13 14:19:45-03
REALM

and i want it to be displayed something like this:

USERNAME
IP-ADDRESS
74
000B2E5B
NULL
6572
IP-ADDRESS
NULL
4
974135985
REALM

i know it is possible using date_part
select date_part( 'epoch' , timestamp '2000-11-13 14:30:40');
gave the right answer . but when i executte
select user_name, date_part( 'epoch' , timestamp 'acct_timestamp') from
tbacct limit 2;

it said ERROR:  Bad timestamp external representation 'acct_timestamp'
how should i represent date_part( 'epoch' , timestamp 'acct_timestamp')
to work?

Thanks in Advance

-- 
Marcelo Bartsch R.
bartschm@psi.com
PSINet Chile

/O /Ot Minimize execution speed (default)
Microsoft C/C++ Compiler Documentation,
'Enviroment and Tools', p531

Telefono         : +56-2-3979000
Numero de Fax    : +56-2-3979090
Numero de eFax   : (815) 366-3177

>  select user_name, date_part( 'epoch' , timestamp 'acct_timestamp') from
> tbacct limit 2;
> 
> it said ERROR:  Bad timestamp external representation 'acct_timestamp'
> how should i represent date_part( 'epoch' , timestamp 'acct_timestamp')
> to work?

select user_name, date_part ('epoch', acct_timestamp) from tbacct limit 2;
should work... the single quotes are making a literal string value, so
your query is saying take the epoch of the timestamp represented by the
literal 'acct_timestamp' rather than the value of the field.