Categories:

System Functions (System Information)

SYSTEM$LAST_CHANGE_COMMIT_TIME

Returns the commit time of the last DML change performed on a table or a view. In case of a view, the function returns the latest commit time of all the objects referenced in the view.

For each DML performed on the specified table or underlying tables in the specified view, the returned value increases; however, this value should not be interpreted as or converted to a timestamp.

Note that the function must be called as a system function, i.e. SYSTEM$LAST_CHANGE_COMMIT_TIME.

Syntax

SYSTEM$LAST_CHANGE_COMMIT_TIME( '<object_name>'  )

Usage Notes

  • object_name specifies the table or view for which the commit time for the last DML is returned.

  • The value returned by the function is the UTC timestamp represented as milliseconds since the beginning of the epoch (i.e. since midnight January 1, 1970). It can be used in applications, such as BI tools, to determine whether the underlying table data has changed. This can be useful for applications that display dashboards and need to figure out whether the dashboard needs to be updated based on new data in the table.

Examples

CALL SYSTEM$LAST_CHANGE_COMMIT_TIME('mytable');

+--------------------------------+
| SYSTEM$LAST_CHANGE_COMMIT_TIME |
|--------------------------------|
|                  1501896165983 |
+--------------------------------+
SELECT SYSTEM$LAST_CHANGE_COMMIT_TIME('mytable');

+--------------------------------+
| SYSTEM$LAST_CHANGE_COMMIT_TIME |
|--------------------------------|
|                  1501896165983 |
+--------------------------------+

INSERT INTO mytable VALUES (2,100), (3,300);

SELECT SYSTEM$LAST_CHANGE_COMMIT_TIME('mytable');

+--------------------------------+
| SYSTEM$LAST_CHANGE_COMMIT_TIME |
|--------------------------------|
|                  1501896192700 |
+--------------------------------+