Yes,But i need to display last digits also
like 1500 08-09-10.738901
On Mon, Jul 13, 2015 at 8:18 PM, Francisco Olarte <folarte@xxxxxxxxxxxxxx> wrote:
Hi Ramesh:On Sun, Jul 12, 2015 at 8:21 AM, Ramesh T <rameshparnanditech@xxxxxxxxx> wrote:postgres queryselect current_timestamp-TO_TIMESTAMP(to_char(DATE1, 'YYYY-MM-DD HH24'|| ' '||'MI'||' '||'SS')||' '||(SELECT utc_offset FROM pg_catalog.pg_timezone_namesWHERE name=DATETIMEZOZE1) , ''YYYY-MM-DD HH24'||' '||'MI'||' '||'SS')::timestamptzgetting result..But in oracle using systimestamp,to_timestamptz and SS TZH is not supporting to_timestamp in postgres.I do not know about Oracle, but in postgres you are substracting to timestamps ( current_timestamp - to_timestamp(whatever) ). This gives you an interval.result..diffrence is days displaying in postgres query..i thnk something wrong. is it..?Days is displaying in postgres query because it is the default format to display intervals ( it's a little more complicated, but related ).
$ select '1500 days 8 hours 9 minutes 10 seconds'::interval;
interval
--------------------
1500 days 08:09:10
(1 row)If you want a particular format you should use the appropiate formatting functions, like to_char
$ select to_char('1500 days 8 hours 9 minutes 10 seconds'::interval,'DDD HH-MI-SS');
to_char
---------------
1500 08-09-10
(1 row)Or, you could try to change the default formatting, but this is generally incorrect.Regards.Francisco Olarte.