1 2 Previous Next 17 Replies Latest reply on Mar 21, 2012 6:48 AM by rp0428

    Timestamp

    Rengudi
      Hi

      When i give in where condition for columna its working fine.


      columna between cast(systimestamp-1 as date) and cast(systimestamp as date)

      I need to extract the data for last five hours for columna for current date how?


      Thanks
      Raj
        • 1. Re: Timestamp
          sb92075
          Rangarajan wrote:
          Hi

          When i give in where condition for columna its working fine.


          columna between cast(systimestamp-1 as date) and cast(systimestamp as date)

          I need to extract the data for last five hours for columna for current date how?


          Thanks
          Raj
          WHERE COLUMNA > SYSDATE-(5/24)
          • 2. Re: Timestamp
            SomeoneElse
            What is the type of your columna?
            • 3. Re: Timestamp
              Rengudi
              timestamp(3)
              • 4. Re: Timestamp
                Frank Kulash
                Hi,

                If columna is a TIMESTAMP, then
                WHERE   columna  BETWEEN  SYSTIMESTAMP - INTERVAL '5' HOUR
                                 AND      SYSTIMESTAMP
                will pick rows where columna is in the last 5 hours.
                • 5. Re: Timestamp
                  Rengudi
                  It's not giving any results. But data is there between the period.
                  • 6. Re: Timestamp
                    Paul  Horth
                    What does
                    select systimestamp from dual;
                    give you?
                    • 7. Re: Timestamp
                      Rengudi
                      3/20/2012 5:11:52.280416 PM +02:00
                      • 8. Re: Timestamp
                        Paul  Horth
                        Ask yourself, what timezone you are in (clue: see what you just posted).

                        Then ask what timezone your data is in.
                        • 9. Re: Timestamp
                          sKr
                          Hi.

                          Please post some sample data in order to test our ideas.

                          Regards.
                          • 10. Re: Timestamp
                            Rengudi
                            doc     sdr  rcvr          id        timestamp
                            Papinet n V2R30        LSC     3/20/2012 2:48:39.477 PM
                            Papinet  us V2R30     LSC     3/20/2012 2:48:39.513 PM
                            Papinet  ion V2R10      LSC     3/20/2012 2:48:39.615 PM
                            Papinet age V2R10      LSC     3/20/2012 2:48:39.739 PM
                            Papinet  ion V2R10      LSC     3/20/2012 2:48:39.900 PM
                            • 11. Re: Timestamp
                              sKr
                              Hi.

                              Frank's solution is working for me.
                              SELECT SYSTIMESTAMP FROM DUAL;
                              
                              2012-03-20 09:21:17.714482000 -06:00
                              
                              WITH data AS
                              (
                                   SELECT TO_TIMESTAMP ('20-03-2012 09:00:00.000000', 'DD-MM-YYYY HH24:MI:SS.FF') columna FROM DUAL UNION
                                   SELECT TO_TIMESTAMP ('20-03-2012 08:00:00.000000', 'DD-MM-YYYY HH24:MI:SS.FF') FROM DUAL UNION
                                   SELECT TO_TIMESTAMP ('20-03-2012 07:00:00.000000', 'DD-MM-YYYY HH24:MI:SS.FF') FROM DUAL UNION
                                   SELECT TO_TIMESTAMP ('20-03-2012 06:00:00.000000', 'DD-MM-YYYY HH24:MI:SS.FF') FROM DUAL UNION
                                   SELECT TO_TIMESTAMP ('20-03-2012 05:00:00.000000', 'DD-MM-YYYY HH24:MI:SS.FF') FROM DUAL UNION
                                   SELECT TO_TIMESTAMP ('20-03-2012 04:00:00.000000', 'DD-MM-YYYY HH24:MI:SS.FF') FROM DUAL UNION
                                   SELECT TO_TIMESTAMP ('20-03-2012 18:00:00.000000', 'DD-MM-YYYY HH24:MI:SS.FF') FROM DUAL
                              )
                              SELECT * FROM data
                              WHERE
                                   columna BETWEEN SYSTIMESTAMP - INTERVAL '5' HOUR AND SYSTIMESTAMP;
                              
                              2012-03-20 05:00:00.000000000
                              2012-03-20 06:00:00.000000000
                              2012-03-20 07:00:00.000000000
                              2012-03-20 08:00:00.000000000
                              2012-03-20 09:00:00.000000000
                              Regards.
                              • 12. Re: Timestamp
                                Frank Kulash
                                Hi,

                                What happens if you use LOCALTIMESTAMP instead of SYSTIMESTAMP?
                                WHERE   columna  BETWEEN  LOCALTIMESTAMP - INTERVAL '5' HOUR
                                                 AND      LOCALTIMESTAMP
                                • 13. Re: Timestamp
                                  Paul  Horth
                                  Frank,

                                  Is it not to do with systimestamp being a timestamp with time zone and, presumably, his data just being a timestamp?

                                  It will try 'promote' the timestamp to a timestamp with time zone in order to do the comparison, using the local time zone setting.

                                  I think that might be at the core of his problem.
                                  • 14. Re: Timestamp
                                    Rengudi
                                    Hi Gurus,

                                    I get the results when i give like this.
                                     Where Columna BETWEEN TO_TIMESTAMP('3/20/2012 6:00:00.313 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM') AND TO_TIMESTAMP('3/21/2012 6:00:03.956 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM')
                                    but, I cant pass static value to generate report automatically for every 5 hours. instead of values i would like give systimestamp and its not working for systimestamp with local time zone.

                                    Thanks
                                    Raj
                                    1 2 Previous Next