Re: timestamp check - Mailing list pgsql-general

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@gmail.com
> <mailto:rameshparnanditech@gmail.com>> 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@peoplecall.com <mailto:folarte@peoplecall.com>> wrote:
>
>         Hi Ramesh:
>
>         On Sun, Jul 12, 2015 at 8:21 AM, Ramesh T
>         <rameshparnanditech@gmail.com
>         <mailto:rameshparnanditech@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..
>
>             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@aklaver.com