Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Querying Timestamp

M.KamranJun 14 2019 — edited Jun 18 2019

Hi

We have a question on the TIMESTAMP data type

We created a test table

SQL> desc test_timestamp

Name                                      Null?    Type

----------------------------------------- -------- ----------------------------

TSTZ                                               TIMESTAMP(6) WITH TIME ZONE

we inserted value into it.. using an existing table

Here are the last few records...

14-APR-17 09.50.36.000000 AM -04:00

28-APR-17 05.46.33.000000 PM -04:00

30-MAY-19 02.58.09.000000 PM -04:00

04-JUN-19 10.11.53.000000 AM -04:00

05-JUN-19 10.11.53.000000 AM -04:00

06-JUN-19 10.11.53.000000 AM -04:00

07-JUN-19 10.11.53.000000 AM -04:00

14-JUN-19 10.11.53.000000 AM -04:00

15-JUN-19 10.11.53.000000 AM -04:00

16-JUN-19 10.11.53.000000 AM -04:00

17-JUN-19 10.11.53.000000 AM -04:00

When we ran this query ...

SQL> select to_char(tstz,'RRRR') YEAR,count(*) from test_timestamp where to_char(systimestamp,'RRRR')='2019' group by to_char(tstz,'RRRR') ;

YEAR   COUNT(*)

---- ----------

2017      99997

2019          9

It gave us the correct result...

But when we ran this query, it is giving us incorrect results..

SQL> select count(*) from test_timestamp where to_char(systimestamp,'MON')='JUN' and to_char(systimestamp,'RRRR')='2019';

  COUNT(*)

----------

    100006

Since there are only 9 records with 2019 date

How exactly should we construct a query to filter the rows based on a particular MONTH and YEAR from a column that is defined as TIMESTAMP with TZ?

Thanks

Comments

Post Details

Added on Jun 14 2019
21 comments
1,318 views