Querying Timestamp

M.Kamran wrote:

...

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?

Avoid using TO_CHAR in conditions; it is there to format data for display.

One option is to use the EXTRACT function:

with data(tstz) as (
  select systimestamp - numtoyminterval(level-1, 'month')
  from dual
  connect by level <= 36
)
select count(*) from data
where extract(year from tstz) = extract(year from systimestamp);

  COUNT(*)
----------
         6

with data(tstz) as (
  select systimestamp - numtoyminterval(level-1, 'month')
  from dual
  connect by level <= 36
)
select count(*) from data
where extract(year from tstz) = 2019
and extract(month from tstz) = 6;

  COUNT(*)
----------
         1

Do you have an index on TSTZ? In that case, you need another solution in order to make use of the index. Let us know.

Best regards,

Stew Ashton

I went ahead and double-checked the solution with an index, so here goes.

If you create an index on a timestamp with time zone column, Oracle creates a hidden system column that contains the timestamp "normalized" to UTC; it uses the SYS_EXTRACT_UTC function for this.

To use that index, your conditions have to refer directly to the column value. For that, compare the column to a range, for example:

create table t(tstz, filler) as
select systimestamp - numtodsinterval(level-1, 'hour'),
rpad('a',99,'a')
from dual
connect by level <= 1000;

create index it on t(tstz);

var from_ts_string varchar2(99);
exec :from_ts_string := '2019-06-14 00:00:00';
var to_ts_string varchar2(99);
exec :to_ts_string := '2019-06-15 00:00:00';

set serveroutput off

select /*+ gather_plan_statistics */ count(*) from t
where from_tz(to_timestamp(:from_ts_string), to_char(systimestamp, 'TZR')) <= tstz
  and tstz < from_tz(to_timestamp(:to_ts_string), to_char(systimestamp, 'TZR'));

  COUNT(*)
----------
        24

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST +PREDICATE'));

----------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.01 |       2 |      4 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.01 |       2 |      4 |
|*  2 |   FILTER           |      |      1 |        |     24 |00:00:00.01 |       2 |      4 |
|*  3 |    INDEX RANGE SCAN| IT   |      1 |      3 |     24 |00:00:00.01 |       2 |      4 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(SYS_EXTRACT_UTC(FROM_TZ(TO_TIMESTAMP(:TO_TS_STRING),TO_CHAR(SYSTIMESTAMP
              (6),'TZR')))>SYS_EXTRACT_UTC(FROM_TZ(TO_TIMESTAMP(:FROM_TS_STRING),TO_CHAR(SYSTIMESTAM
              P(6),'TZR'))))
   3 - access("T"."SYS_NC00003$">=SYS_EXTRACT_UTC(FROM_TZ(TO_TIMESTAMP(:FROM_TS_STRING
              ),TO_CHAR(SYSTIMESTAMP(6),'TZR'))) AND "T"."SYS_NC00003$"<SYS_EXTRACT_UTC(FROM_TZ(TO_T
              IMESTAMP(:TO_TS_STRING),TO_CHAR(SYSTIMESTAMP(6),'TZR'))))

This solution assumes that you want to base your ranges on the operating system time zone of your database server. If you base the ranges on some other time zone, the results will be different because time zone differences will cause some rows to be on different days, or even years.

Regards,

Stew

The OP's attempt, flawed as it is, suggests that they are looking for rows where the timestamp is in June 2019 in the time zone OF THAT ROW. That's what TO_CHAR would do, if it were applied to the column and not to something else. That is also what EXTRACT does.

I wasn't aware that an index on a timestamp with time zone converts to UTC (and I find it annoying, because that loses information). If so, then I don't see how such an index could be used to get the result the OP seems to desire, since the time zone information is not present in the index. Instead, to get the same result as the other queries (if indeed they care about year and month at the same time zone as encoded in TSTZ), they could instead use a function-based index, on CAST(TSTZ AS TIMESTAMP), and then write the conditions the usual way, not on TSTZ but on this expression.

mathguy wrote:

 

The OP's attempt, flawed as it is, suggests that they are looking for rows where the timestamp is in June 2019 in the time zone OF THAT ROW. That's what TO_CHAR would do, if it were applied to the column and not to something else. That is also what EXTRACT does.

True, so my two solutions could return different results.

Which result is desired? Only the OP or those who request the data can know for sure. I'm not sure about your interpretation, though, since all the rows have the same timestamp and it looks like SYSTIMESTAMP was used to populate the column.

Regards,

Stew

Hello M,

<<to ensure the DELETE query uses INDEX instead of Full Table Scan>>

Note that this is maybe not the optimal way... If you have to access many rows to delete them, the optimizer might estimate that a FTW is "cheaper" than using an index to identify the rows "one by one" because using the index can cause a lot of work with a lot of repetitions of something like: "locate next row in the index: next row is row X in block Y -> fetch this block, delete the row, come back to the index range scan and repeat" (without considering the work of maintaining the indexes). On the other hand a "full scan" will scan each block of the table one time and once processed, zero one or several rows will have been deleted but Oracle will not have to revisit the block.

You might search some info about "CLUF", "index clustering factor", to have more explanations.

So maybe another proposal if you delete a large number of rows is to work "in the opposite direction": define the rows that you want to keep and build a new table to contain these ones and only these ones, then replace the original table by the new one.

Beware that this has quite some implications: during the operation, other sessions should not modify the data in the original table (better to work during a maintenance window), and at the end of the "keep what has to be kept", you have to recreate all the indexes on the new version of the table (and if you want to keep the same names you have first to drop them on original table but be sure to have the correct DDL before dropping them), you have also to define the constraints if any, you might have to take care of FK (especially FK pointing to the original table) and don't forget to take care of the privileges (also to do: drop original table or at least rename it, and rename new table with original name)

As you see it is more complex than a "simple" statement "DELETE mytable WHERE many_rows_impacted;" but it can really be worth the effort... (of course: to test in non-prod to get familiar with the concept)

Best regards,

Bruno

M.Kamran wrote:

 

Now all we need is what would be the correct SQL construct to filter out the 6 months data?

 

Thanks

How many more years here before you start writing your own simple  SELECT statement with working WHERE clause?

Show us YOUR SQL statement first

M.Kamran wrote:

 

Hello

Well we tried posting our query twice but it seems this portal is having some issues..

we will continue to try until it is posted and then you can take a look

 

thanks

Portal having issues! RIGHT! When nobody else reports any problem posting SQL here.

SELECT LAST_NAME FROM EMPLOYEES WHERE HIRE_DATE < SYSDATE-180;