Sunday, September 5, 2010

explicit type converstion in postgresql

To convert value from one type to another use explicit type conversion function

1) to_timestamp -- used to convert bigint to timestamp with or with out timezone.
2) epoch -- used to convert timestamp to bigint value.

here see the example

1) to_timestamp

SELECT to_timestamp(1283437580);
-- it returns the output like

to_timestamp
timestamp with time zone
----------------------
"2010-09-02 19:56:20+05:30"

want to display the timestamp with out timezone

SELECT to_timestamp(1283437580)::timestamp;

to_timestamp
timestamp with time zone
----------------------
"2010-09-02 19:56:20"

2) epoch

SELECT EXTRACT(epoch FROM current_timestamp(0)) -- it return output like

date_part
double precision
-------------------
1283753630

-- more explicit type conversion will update here...
Post a Comment