1 2 Previous Next 21 Replies Latest reply on Jun 18, 2019 4:19 PM by Sven W.

    Querying Timestamp

    M.Kamran

      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

        • 1. Re: Querying Timestamp
          mNem

          M.Kamran wrote:

           

           

          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';

           

          The WHERE clause evaluates to true for each row of the table since the current month and current year are checked against the SYSTIMESTAMP. Therefore, count(*) returns the total number of rows in the table.

           

          You may want to change the query to ...

           

          select count(*) from test_timestamp where to_char(tstz,'MON')='JUN' and to_char(tstz,'YYYY')='2019';

          1 person found this helpful
          • 2. Re: Querying Timestamp
            Stew Ashton

            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

            • 3. Re: Querying Timestamp
              BrunoVroman

              Hello,

               

              the error is in the query itself, putting condition on systimestamp... As we are indeed in June 2019, your queries are equivalent to

               

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

              YEAR   COUNT(*)

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

              2017      99997

              2019          9

               

              SQL> select count(*) from test_timestamp;

                COUNT(*)

              ----------

                  100006

               

              So I guess you agree now that these are not wrong results (because the results are what is queried) but wrong queries (because the queries are not what you want to ask)  ;-)

              As already mentioned, you just have to build the query with conditions on tstz.

               

              Best regards,

               

              Bruno Vroman.

              1 person found this helpful
              • 4. Re: Querying Timestamp
                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

                • 5. Re: Querying Timestamp
                  mathguy

                  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.

                  • 6. Re: Querying Timestamp
                    Stew Ashton

                    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

                    • 7. Re: Querying Timestamp
                      M.Kamran

                      Thanks mNem

                       

                      That worked

                       

                      Thanks a lot

                       

                      Now if we want to filter out data based on the TSTZ column that is older than 6 months,  what would be the correct query construct for that?

                      Can we use > systimestamp?

                      The idea is to purge records from a table that has TIMESTAMP TSTZ  and we run that script on the first of every month and delete records older than 6 months old

                       

                      Thanks

                      • 8. Re: Querying Timestamp
                        M.Kamran

                        And oh depending on what query construct we should use for filtering out the data older then 6 month, please kindly suggest as to what kind of index we should create to ensure the DELETE query uses INDEX instead of Full Table Scan

                         

                        Thanks again for all your help and guidance

                        • 9. Re: Querying Timestamp
                          BrunoVroman

                          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

                          • 10. Re: Querying Timestamp
                            M.Kamran

                            Hi Bruno

                            Totally agree with your suggestion

                            Once we have purged the table initially and reduced it to have only 6 months data, from that point on, since we will be running it on a monthly basis, there wont be much data to play with for DELETE operations

                            We are thinking about using the following approach for first time

                            create a new table with only last 6 months data (call it table B)

                            Rename the original table to table C

                            Rename the table B to the original table

                            truncate table C

                             

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

                             

                            Thanks

                            • 11. Re: Querying Timestamp
                              John Thorton

                              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

                              • 12. Re: Querying Timestamp
                                M.Kamran

                                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

                                • 13. Re: Querying Timestamp
                                  M.Kamran

                                  To create a new table holding only last 6 months data .. something along these lines we were thinking

                                   

                                  create table abc as select * from test_timestamp where tstz >=systimestamp -180;

                                   

                                  Please let us know if this would be correct or should we tweak it

                                   

                                  Thanks

                                  • 14. Re: Querying Timestamp
                                    John Thorton

                                    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;

                                    1 2 Previous Next