i added .MS getting values,But Problem query keep on running but not
displaying results,when i add like limit 5.it is return values..
what is the problem with query..?
changed date and changed_dttimezone are are parameters..
select to_char((current_timestamp -
TO_TIMESTAMP(to_char(chaged_date,'YYYY-MM-DD HH24'|| ' '||'MI'||'
'||'SS')||' '||(SELECT utc_offset FROM pg_catalog.pg_timezone_names
WHERE name=changed_dttimezone), 'YYYY-MM-DD HH24'||' '||'MI'||'
'||'SS')::timestamptz),'DDD HH:MI:SS.MS')
On Tue, Jul 14, 2015 at 4:23 PM, Ramesh T <rameshparnandit...@gmail.com>
wrote:
> Yes,But i need to display last digits also
>
> [image: Inline image 1]
>
> like 1500 08-09-10.738901
>
> On Mon, Jul 13, 2015 at 8:18 PM, Francisco Olarte <fola...@peoplecall.com>
> wrote:
>
>> Hi Ramesh:
>>
>> On Sun, Jul 12, 2015 at 8:21 AM, Ramesh T <rameshparnandit...@gmail.com>
>> wrote:
>>
>>> postgres query
>>> select current_timestamp-
>>> TO_TIMESTAMP(to_char(DATE1, 'YYYY-MM-DD HH24'|| ' '||'MI'||' '||'SS')||'
>>> '||(SELECT utc_offset FROM pg_catalog.pg_timezone_names
>>> WHERE name=DATETIMEZOZE1) , ''YYYY-MM-DD HH24'||' '||'MI'||'
>>> '||'SS')::timestamptz
>>>
>>> getting result..
>>>
>>> [image: Inline image 1]
>>>
>>>
>>> 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..
>>>
>>> [image: Inline image 2]
>>>
>>> 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.
>>
>>
>>
>
