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