timestamp check

On Fri, Jul 10, 2015 at 8:54 AM, Ramesh T <rameshparnanditech@gmail.com>
wrote:

select current_timestamp-to_timestamp(to_char(current_date,'YYYY-MM-DD
HH24'||':'||'MI'||':'||'SS')||' '||(SELECT utc_offset FROM
pg_catalog.pg_timezone_names
WHERE name='US/Eastern'),'YYYY-MM-DD
HH24'||':'||'MI'||':'||'SS')::timestamptz;

it's not displaying timezone..any help..?

​You haven't told us how you are executing the above query.

It also seems like an awfully convoluted answer to whatever query you are
asking.

David J.
​

okay,i'm executing a query from pgadmin3.

i want display time with timezone.But above query displaying date and time
not timezone...

On Sat, Jul 11, 2015 at 9:34 PM, David G. Johnston <
david.g.johnston@gmail.com> wrote:

On 07/10/2015 05:54 AM, Ramesh T wrote:

select current_timestamp-to_timestamp(to_char(current_date,'YYYY-MM-DD
HH24'||':'||'MI'||':'||'SS')||' '||(SELECT utc_offset FROM
pg_catalog.pg_timezone_names
WHERE name='US/Eastern'),'YYYY-MM-DD
HH24'||':'||'MI'||':'||'SS')::timestamptz;

it's not displaying timezone..any help..?

That would seem to depend on what is happening in the
current_timestamp-to_timestamp function.

So what is current_timestamp-to_timestamp doing or more importantly
what is it returning before the timestamptz cast?

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 10/07/2015 13:54, Ramesh T wrote:

select current_timestamp-to_timestamp(to_char(current_date,'YYYY-MM-DD
HH24'||':'||'MI'||':'||'SS')||' '||(SELECT utc_offset FROM
pg_catalog.pg_timezone_names
WHERE name='US/Eastern'),'YYYY-MM-DD
HH24'||':'||'MI'||':'||'SS')::timestamptz;

it's not displaying timezone..any help..?

Because TIMESTAMPTZ - TIMESTAMPTZ = INTERVAL, not TIMESTAMPTZ.

Also, why on earth are you doing all those string concatenations in the
to_char() calls? Why not just do to_char(..., 'YYYY-MM-DD H24:MI:SS')?

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 07/10/2015 05:54 AM, Ramesh T wrote:

select current_timestamp-to_timestamp(to_char(current_date,'YYYY-MM-DD
HH24'||':'||'MI'||':'||'SS')||' '||(SELECT utc_offset FROM
pg_catalog.pg_timezone_names
WHERE name='US/Eastern'),'YYYY-MM-DD
HH24'||':'||'MI'||':'||'SS')::timestamptz;

it's not displaying timezone..any help..?

Forget my previous post, Rays post made me realize the error of my ways,
namely thinking current_timestamp-to_timestamp was a function.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

result..

[image: Inline image 2]

diffrence is days displaying in postgres query..i thnk something wrong. is
it..?

any help apprictiated.

On Sat, Jul 11, 2015 at 11:12 PM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

any help..?

On Sun, Jul 12, 2015 at 11:51 AM, Ramesh T <rameshparnanditech@gmail.com>
wrote:

On 07/11/2015 11:21 PM, Ramesh T 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..

First there is no indication of what DATE1 and DATETIMEZOZE1 are, so the
result you show has no context.

Second why are you doing all this? You are taking a timestamp (I
presume) converting it to a string and then converting it back to a
timestamp, just to to timestamp subtraction.

From what I can piece together you seem to be wanting to get the
interval between two timestamps, is this correct?

Is so or if not, show us a schematic representation of what you are
trying to achieve. For example:

'07/13/2015 07:03:15'::timestamp - '01/01/2015 14:15:00'::timestamp

= interval in days, hours, etc

Inline image 1

But in oracle using systimestamp,to_timestamptz and SS TZH is not
supporting to_timestamp in postgres.

result..

Inline image 2

diffrence is days displaying in postgres query..i thnk something wrong.
is it..?

any help apprictiated.

On Sat, Jul 11, 2015 at 11:12 PM, Adrian Klaver
<adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:

On 07/10/2015 05:54 AM, Ramesh T wrote:

select
current_timestamp-to_timestamp(to_char(current_date,'YYYY-MM-DD
HH24'||':'||'MI'||':'||'SS')||' '||(SELECT utc_offset FROM
pg_catalog.pg_timezone_names
WHERE name='US/Eastern'),'YYYY-MM-DD
HH24'||':'||'MI'||':'||'SS')::timestamptz;

it's not displaying timezone..any help..?

Forget my previous post, Rays post made me realize the error of my
ways, namely thinking current_timestamp-to_timestamp was a function.

--
Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 11/07/2015 17:11, Ramesh T wrote:

okay,i'm executing a query from pgadmin3.

i want display time with timezone.But above query displaying date and
time not timezone...

On Sat, Jul 11, 2015 at 9:34 PM, David G. Johnston
<david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>> wrote:

On Fri, Jul 10, 2015 at 8:54 AM, Ramesh T
<rameshparnanditech@gmail.com
<mailto:rameshparnanditech@gmail.com>>wrote:

select
current_timestamp-to_timestamp(to_char(current_date,'YYYY-MM-DD
HH24'||':'||'MI'||':'||'SS')||' '||(SELECT utc_offset FROM
pg_catalog.pg_timezone_names
WHERE name='US/Eastern'),'YYYY-MM-DD
HH24'||':'||'MI'||':'||'SS')::timestamptz;

it's not displaying timezone..any help..?

Hi there,

This question was answered a few days ago, but perhaps you didn't see
it. The reason you don't get timezone information is that subtracting
two timestamps results in an interval value. See table 9-27 on this page:

http://www.postgresql.org/docs/9.4/static/functions-datetime.html

Also, please don't top-post; the convention on this list is to
bottom-post. Thanks! :-)

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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 <folarte@peoplecall.com>
wrote:

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 <rameshparnanditech@gmail.com>
wrote:

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&gt; 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&gt;&#39;)

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general