1 2 Previous Next 15 Replies Latest reply: Aug 27, 2014 3:06 AM by BluShadow RSS

    Specifications for SYSDATE

    1016493

      Hello,

      Is there any resource which defines behavior of the SYSDATE function? For example what is its behavior within a loop? Or multiple loops within same function.

      The documentation @ SYSDATE seems very light. Is there any other resource which provides a more detailed specification?

      Thank you

      Sameer

        • 1. Re: Specifications for SYSDATE
          Antonio Navarro

          Many times Oracle doc get short. Maybe Oracle Press Books have something more but it isn't free.

           

          HTH - Antonio NAVARRO

          • 2. Re: Specifications for SYSDATE
            _Karthick_

            What kind of behavior are you looking at? SYSDATE return the OS date and pretty much thats it. Nothing much (beyond the document) could be said .

            • 3. Re: Specifications for SYSDATE
              Roger

              SYSDATE is not deterministic ... so every call to the function will return a "new" return value.

               

              So if you need the "call" to return the same date within a loop - then you have to assign sysdate to a variable outside the loop and use that variable instead of a call to sysdate.

               

              it is as easy as that.

               

              hth

              • 4. Re: Specifications for SYSDATE
                BluShadow

                As others have mentioned, SYSDATE returns the current date and time (to the second) at the time that it's called within the code.

                If it's used directly within an SQL statement then, due to Oracle database read consistency, SYSDATE will be the same for the duration of the whole query.

                 

                e.g. PL loop of sysdate...

                 

                SQL> ed
                Wrote file afiedt.buf

                  1  begin
                  2    for i in 1 .. 10
                  3    loop
                  4      dbms_output.put_line(to_char(sysdate,'DD/MM/YYYY HH24:MI:SS'));
                  5      dbms_lock.sleep(1);
                  6    end loop;
                  7* end;
                SQL> /
                26/08/2014 08:25:15
                26/08/2014 08:25:16
                26/08/2014 08:25:17
                26/08/2014 08:25:18
                26/08/2014 08:25:19
                26/08/2014 08:25:20
                26/08/2014 08:25:21
                26/08/2014 08:25:22
                26/08/2014 08:25:23
                26/08/2014 08:25:24

                 

                PL/SQL procedure successfully completed.

                 

                SQL loop of sysdate

                 

                SQL> ed
                Wrote file afiedt.buf

                  1  declare
                  2    cursor cur_query is
                  3      select sysdate dt
                  4      from   dual
                  5      connect by level <= 10;
                  6  begin
                  7    dbms_output.put_line('Loop started at: '||to_char(sysdate,'DD/MM/YYYY HH24:MI:SS'));
                  8    for i in cur_query
                  9    loop
                10      dbms_output.put_line(to_char(i.dt,'DD/MM/YYYY HH24:MI:SS'));
                11      dbms_lock.sleep(1);
                12    end loop;
                13    dbms_output.put_line('Loop ended at: '||to_char(sysdate,'DD/MM/YYYY HH24:MI:SS'));
                14* end;
                SQL> /
                Loop started at: 26/08/2014 08:27:08
                26/08/2014 08:27:08
                26/08/2014 08:27:08
                26/08/2014 08:27:08
                26/08/2014 08:27:08
                26/08/2014 08:27:08
                26/08/2014 08:27:08
                26/08/2014 08:27:08
                26/08/2014 08:27:08
                26/08/2014 08:27:08
                26/08/2014 08:27:08
                Loop ended at: 26/08/2014 08:27:18

                 

                PL/SQL procedure successfully completed.

                 

                As you can see in this second example, even though the complete loop through the query took 10 seconds to complete, the sysdate returned by the query for each row was consistent at the time the query was executed.  This is read consistency.

                • 5. Re: Specifications for SYSDATE
                  1016493

                  Hello,

                  Thank you for replying. we observed the same behavior too.

                  We had experienced problems migrating sysdate to its PostgreSQL equivalent, depending on where its used.. So this question was part of an effort to figure out every possible scenario  of sysdate usage.Is there anyway  where i could get sysdate behavior wrt timezone, triggers, subselects etc? Its difficult to imagine and test for all scenarios, hence a specification document

                  regards

                  Sameer

                  • 6. Re: Specifications for SYSDATE
                    BluShadow

                    Timezone requires a TIMESTAMP datatype rather than DATE datatype (which is what SYSDATE is).  For that you'll need to look up the TIMESTAMP datatype and SYSTIMESTAMP.

                     

                    Triggers are PL code so behave the same as PL code anywhere.

                    Sub Selects are SQL code so behave the same as SQL code (i.e. they have read-consistency in line with the main query).

                     

                    There's not that many scenarios.... PL code or SQL code.

                     

                    The documentation is actually quite clear about these things, though the information may be seen as being spread out a bit depending on what context you're looking at it.

                    If you have a specific concern, then please state it.

                    • 7. Re: Re: Specifications for SYSDATE
                      Rahul_India

                      Hi Blu,

                       

                      Thanks for the clearing up things.You gave a similar example demonstrating pipelined functions

                       

                      If it's used directly within an SQL statement then, due to Oracle database read consistency, SYSDATE will be the same for the duration of the whole query.

                      In the below case we are using sysdate in PL/SQL so we should get diffrenet value of sysdate for each iteration.Or sysdate behaves the same way as in SQL giving same value for each  iteration.

                       

                       

                      begin
                          for i in 1 .. 10
                          loop
                           dbms_output.put_line(to_char(sysdate,'DD/MM/YYYY HH24:MI:SS'));
                      end loop;
                         end;
                      
                      
                      • 8. Re: Re: Re: Specifications for SYSDATE
                        BluShadow

                        Rahul_India wrote:

                         

                        Hi Blu,

                         

                        Thanks for the clearing up things.You gave a similar example demonstrating pipelined functions

                         

                        If it's used directly within an SQL statement then, due to Oracle database read consistency, SYSDATE will be the same for the duration of the whole query.

                        In the below case we are using sysdate in PL/SQL so we should get diffrenet value of sysdate for each iteration.Or sysdate behaves the same way as in SQL giving same value for each  iteration.

                         

                         

                        1. begin 
                        2.     for i in 1 .. 10 
                        3.     loop 
                        4.     dbms_output.put_line(to_char(sysdate,'DD/MM/YYYY HH24:MI:SS')); 
                        5. end loop; 
                        6. end;

                         

                        In that case it is PL/SQL code, running that fast that it will generate the 10 date/time values within a fraction of a second.  There's a chance that it may overlap 2 seconds, but typically you'll see the same time because it's so fast.  That's why I used a dbms_lock.sleep in my example to slow it down.

                         

                        As for pipelined functions, it's calling PL/SQL code which generates the row of data for each fetch against the "table", so the SYSDATE isn't being used directly in the SQL and will differ for each call (again it's fast so I'm using dbms_lock.sleep to demonstrate it changing)...

                         

                        SQL> create type tDt as table of date
                          2  /

                         

                        Type created.

                         

                        SQL> create or replace function tenDates return tDt pipelined as
                          2  begin
                          3    for i in 1 .. 10
                          4    loop
                          5      pipe row(sysdate);
                          6      dbms_lock.sleep(1);
                          7    end loop;
                          8    return;
                          9  end;
                        10  /

                         

                        Function created.

                         

                        SQL> select *
                          2  from   table(tenDates())
                          3  /

                         

                        COLUMN_VALUE
                        --------------------
                        26-AUG-2014 13:16:24
                        26-AUG-2014 13:16:25
                        26-AUG-2014 13:16:26
                        26-AUG-2014 13:16:27
                        26-AUG-2014 13:16:28
                        26-AUG-2014 13:16:29
                        26-AUG-2014 13:16:30
                        26-AUG-2014 13:16:31
                        26-AUG-2014 13:16:32
                        26-AUG-2014 13:16:33

                         

                        10 rows selected.

                        • 9. Re: Re: Re: Re: Specifications for SYSDATE
                          BluShadow


                          Likewise if the dates are obtained within the pipelined function from a query, then that query has read consistency...

                           

                          SQL> create or replace function tenDates return tDt pipelined as
                            2    cursor cur_dt is
                            3      select sysdate as dt
                            4      from   dual
                            5      connect by level <= 10;
                            6  begin
                            7    for i in cur_dt
                            8    loop
                            9      pipe row(i.dt);
                          10      dbms_lock.sleep(1);
                          11    end loop;
                          12    return;
                          13  end;
                          14  /

                           

                          Function created.

                           

                          SQL> select *
                            2  from   table(tenDates())
                            3  /

                           

                          COLUMN_VALUE
                          --------------------
                          26-AUG-2014 13:23:05
                          26-AUG-2014 13:23:05
                          26-AUG-2014 13:23:05
                          26-AUG-2014 13:23:05
                          26-AUG-2014 13:23:05
                          26-AUG-2014 13:23:05
                          26-AUG-2014 13:23:05
                          26-AUG-2014 13:23:05
                          26-AUG-2014 13:23:05
                          26-AUG-2014 13:23:05

                           

                          10 rows selected.

                          • 10. Re: Specifications for SYSDATE
                            Rahul_India

                            BluShadow Ok got the above point.

                             

                            If it's used directly within an SQL statement then, due to Oracle database read consistency, SYSDATE will be the same for the duration of the whole query.

                             

                            But how will read consistency of oracle would be violated if sysdat in sql would return different values?

                            • 11. Re: Specifications for SYSDATE
                              Frank Kulash

                              Hi,

                               

                              Rahul_India wrote:

                              ...

                              But how will read consistency of oracle would be violated if sysdat in sql would return different values?

                              ...

                              You've said it yourself: If SYSDATE returned different values in the same statement, that would be inconsistent, by definition.

                               

                              Say you're running a query on a particular table, and after you start the query, I UPDATE the same table.  You won't see the results of my UPDATE statement in your results.  Do you understand why it would be inconsistent if you did?

                              SYSDATE changing because the clock ticked would be just as inconsistent as the table changing because I UPDATEd it.

                              • 12. Re: Specifications for SYSDATE
                                2683628

                                Hi

                                 

                                I am struggling with this a bit. If you had long running SQL query, which called SYSDATE, why wouldn't it reflect the current time rather than the first time it was invoked for the SQL statement ?

                                • 13. Re: Re: Specifications for SYSDATE
                                  Frank Kulash

                                  Hi,

                                   

                                  2683628 wrote:

                                   

                                  Hi

                                   

                                  I am struggling with this a bit. If you had long running SQL query, which called SYSDATE, why wouldn't it reflect the current time rather than the first time it was invoked for the SQL statement ?

                                  You don't want to see the effects of the ticking clock in your query for exactly the same reason that you don't want to see the effects of somebody else's UPDATE.

                                   

                                  Say you have a query like this:

                                   

                                  SELECT  COUNT (*)  AS n_active

                                  FROM    table_x

                                  WHERE  status = 'ACTIVE';

                                   

                                  Let's say that 1000 rows have status='ACTIVE', but I'm doing an UPDATE to change that number to 5000.

                                  If you start your query before I start my UPDATE statement, you should see 1000 as the results.

                                  If you start your query after I run my UPDATE statement, you should see 5000 as the results.

                                  Under no circumstances should you see 1001, or 4999, or any number in between, even though that's how many rows my statement had changed at the time you happened to read the table.  That would be inconsistent.  All 4000 rows must change together.  Your query either reflects the state of the table before my UPDATE, or after it, not some sort of half-way state.

                                   

                                  Do you understand that much?

                                  Now consider this query:

                                   

                                  SELECT  COUNT (*)  AS n_active

                                  FROM    table_x

                                  WHERE  modified_date < SYSDATE - 30;

                                  Imagine that 1000 rows have modified_date much older than 30 days ago, and 4000 rows all have modified_date as exactly 17:00:00 30 days ago.

                                  If you start running this query at 17:00:00, you should see 1000 as the results

                                  If you start running this query at 17:00:01, you should see 5000 as the results.

                                  Under no circumstances should you see 1001, or 4999, or any number in between, even though that's how many rows were read after the system clock changed from 17:00:00 to 17:00:01.  That would be inconsistent.  All 4000 rows must change together.  Your query either reflects the conditions at 17:00:00 or at 17:00:01, not some sort of half-way state.

                                  • 14. Re: Re: Specifications for SYSDATE
                                    2683628

                                    Hi

                                     

                                    Thanks for your explanation - I understand it now - however I still tend to dis-agree. :-)

                                    SYSDATE should be what it is - a point in time. Like it or not, not all records are updated in the same second. If you want to engineer that you should first capture the time into a bind variable and use this in the SQL update statement.

                                    1 2 Previous Next