On 07/14/2015 07:13 AM, Ramesh T wrote:
i added .MS getting values,But Problem query keep on running but not
displaying results,when i add like limit 5.it <http://5.it> is return
values..
what is the problem with query..?
As has been explained several times already, subtracting one timestamp
from another is going to get an interval not a timestamp:
postgres@production=# select current_timestamp - '01/01/2015'::timestamp;
?column?
--------------------------
194 days 10:37:33.709606
(1 row)
That cannot be turned into a date:
postgres@production=# select to_char(current_timestamp -
'01/01/2015'::timestamp, 'DDD HH:MI:SS.MS');
to_char
------------------
194 10:39:06.994
(1 row)
That is not going to change.
So the question remains:
What are you trying to do, get an interval or get a timestamp?
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 <http://SS.MS>')
On Tue, Jul 14, 2015 at 4:23 PM, Ramesh T <rameshparnanditech@xxxxxxxxx
<mailto:rameshparnanditech@xxxxxxxxx>> wrote:
Yes,But i need to display last digits also
Inline image 1
like 1500 08-09-10.738901
On Mon, Jul 13, 2015 at 8:18 PM, Francisco Olarte
<folarte@xxxxxxxxxxxxxx <mailto:folarte@xxxxxxxxxxxxxx>> wrote:
Hi Ramesh:
On Sun, Jul 12, 2015 at 8:21 AM, Ramesh T
<rameshparnanditech@xxxxxxxxx
<mailto:rameshparnanditech@xxxxxxxxx>> 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..
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..
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.
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
