6 Replies Latest reply on Sep 16, 2016 8:53 AM by Steven Carter

    calculating time interval

    Krishna Priya Jeganathan

      Hi all,

       

      MODIFIED_DATE < systimestamp - interval '24:00' hour to minute

      how can I write this function in Tableau ?

      if I use today() the date is retrieved, so how can I over come this ?

        • 1. Re: calculating time interval
          Steven Carter

          Hi Krishna,

           

          The DATEADD() function might be what you're looking for. The below would subtract 24 hours from the current date/time.

           

          DATEADD('hour', -24, today())

           

          If that doesn't answer your question let me know. Hopefully I can help with some more detail. Good luck!

           

          Steven

          1 of 1 people found this helpful
          • 2. Re: calculating time interval
            Krishna Priya Jeganathan

            thanks Steven

            so it will result of the function will be just in hours right ?

            I'm trying to apply a condition where the result is less than a modified date which is a date time format.

            how can I do that ?

            • 3. Re: calculating time interval
              Steven Carter

              Hi Krishna,

               

              The result of the formula would also be in datetime. In my example it would be the current datetime minus 24 hours. You could display the result as a date or as a datetime.

               

              Happy to help more if you can provide the workbook or an example with expected result. Hope this helps!

               

              Steven

              • 4. Re: calculating time interval
                kettan

                MODIFIED_DATE < systimestamp - interval '24:00' hour to minute

                how can I write this function in Tableau ?

                if I use today() the date is retrieved, so how can I over come this ?

                NOW() returns current date and time ( hour, minute and second ):

                 

                [MODIFIED_DATE] < DATEADD('hour', -24, NOW())

                 

                Oracle:

                select systimestamp, systimestamp - interval '24:00' hour to minute from dual

                 

                "SYSTIMESTAMP""SYSTIMESTAMP-INTERVAL'24:00'HOURTOMINUTE"
                16-SEP-16 07.56.26.832000000 AM +01:0015-SEP-16 07.56.26.832000000 AM +01:00

                 

                Tableau:

                NOW()DATEADD('hour', -24, NOW())
                16-09-2016 07:56:2615-09-2016 07:56:26

                 


                Tableau Onlinehelp > Date Functions

                NOW( )

                Returns the current date and time.

                The return varies depending on the nature of the connection:

                • For a live, unpublished connection, NOW returns the data source server time.
                • For a live, published connection, NOW returns the data source server time.
                • For an unpublished extract, NOW returns the local system time.
                • For a published extract, NOW returns the local time of the Tableau Server Data Engine. When there are multiple worker machines indifferent time zones, this can produce inconsistent results.

                Example

                NOW( ) = 2004-04-15 1:08:21 PM

                TODAY( )

                Returns the current date.

                Example

                TODAY( ) = 2004-04-15

                • 5. Re: calculating time interval
                  Krishna Priya Jeganathan

                  Thanks Kettan , while the answer by steven worked but I now got to know about the now() function too.

                  • 6. Re: calculating time interval
                    Steven Carter

                    Thanks Krishna! You may want to combine our answers actually. today() will just return the current date, but now() will return the current datetime (including current hours / minutes). Use whichever works best for your use case. Glad this worked out!