Caché SQL Reference
CURRENT_TIMESTAMP
[Back] [Next]
   
Server:docs1
Instance:LATEST
User:UnknownUser
 
-
Go to:
Search:    

A date/time function that returns the current local date and time.
Synopsis
CURRENT_TIMESTAMP[(precision)]
Arguments
precision Optional — Specifies the time precision as the number of digits of fractional seconds. Valid values are the integers 0 through 9. The default is 0 (no fractional seconds); this default is configurable.
Description
CURRENT_TIMESTAMP takes either no arguments or a precision argument, and returns the current date and time as a TIMESTAMP data type. Empty argument parentheses are not permitted. CURRENT_TIMESTAMP returns the current local date and time for this timezone; it adjusts for local time variants, such as Daylight Saving Time.
The datetime string is stored and returned in the following format:
yyyy-mm-dd hh:mm:ss.ffff
Where “f” represents fractional seconds of precision. You can use $HOROLOG to store or return the current local date and time in internal format.
To change the default datetime string format, use the SET OPTION command with the various date and time options.
You can specify CURRENT_TIMESTAMP, with or without precision, as the field default value when defining a datetime field using CREATE TABLE or ALTER TABLE.
Fractional Seconds Precision
CURRENT_TIMESTAMP can return up to nine digits of precision. The number of digits of precision returned is set using the precision argument. The default for the precision argument can be configured using the following:
Specify an integer 0 through 9 (inclusive) for the default number of decimal digits of precision to return. The default is 0. The actual precision returned is platform dependent; precision digits in excess of the precision available on your system are returned as zeroes.
Date and Time Functions Compared
The GETDATE and NOW functions can also be used to return the current local date and time as a TIMESTAMP data type. GETDATE supports precision, NOW does not support precision.
The SYSDATE function is identical to CURRENT_TIMESTAMP, with the exception that SYSDATE does not support precision. CURRENT_TIMESTAMP is the preferred Caché SQL function; SYSDATE is provided for compatibility with other vendors.
To return just the current date, use CURDATE or CURRENT_DATE. To return just the current time, use CURRENT_TIME or CURTIME. These functions return their values in DATE or TIME data type. None of these functions support precision.
The TIMESTAMP data type storage format and display format are the same. The TIME and DATE data types store their values as integers in $HOROLOG format; when displayed in SQL they are converted to date or time display format. Embedded SQL returns them in logical (storage) format by default. You can change the Embedded SQL returned value format using the #SQLCompile Select macro preprocessor directive, as described in the “ObjectScript Macros and the Macro Preprocessor” chapter of Using Caché ObjectScript.
You can use the CAST or CONVERT function to change the data type of dates and times.
Note that all Caché SQL time and date functions except GETUTCDATE are specific to the local time zone setting. To get a current timestamp that is universal (independent of time zone) you can use GETUTCDATE or the Caché ObjectScript $ZTIMESTAMP special variable.
Examples
The following example returns the current local date and time three different ways: in timestamp format with full seconds, in timestamp format with a precision of two digits of fractional seconds, and in $HOROLOG internal storage format with full seconds:
SELECT 
   CURRENT_TIMESTAMP AS FullSecStamp,
   CURRENT_TIMESTAMP(2) AS FracSecStamp,
   $HOROLOG AS InternalFullSec
 
The following Embedded SQL example sets a locale default precision. The first CURRENT_TIMESTAMP specifies no precision; it returns the current time with the locale default precision number of zeroes appended. The second CURRENT_TIMESTAMP specifies precision; this overrides the locale default precision. The precision argument can be larger or smaller than the locale default:
InitialVal
  SET pre=##class(%SYS.NLS.Format).GetFormatItem("TimePrecision")
ChangeVal
  SET x=##class(%SYS.NLS.Format).SetFormatItem("TimePrecision",4)
  &sql(SELECT CURRENT_TIMESTAMP,CURRENT_TIMESTAMP(2) INTO :a,:b)
  IF SQLCODE'=0 {
    WRITE !,"Error code ",SQLCODE }
  ELSE {
    WRITE !,"Timestamp is:  ",a
    WRITE !,"Timestamp is:  ",b }
RestoreVal
  SET x=##class(%SYS.NLS.Format).SetFormatItem("$TimePrecision",pre)
 
The following Embedded SQL example compares local (time zone specific) and universal (time zone independent) time stamps:
  SET b=$ZDATETIME($ZTIMESTAMP,3)
  &sql(SELECT CURRENT_TIMESTAMP INTO :a)
  IF SQLCODE'=0 {
    WRITE !,"Error code ",SQLCODE }
  ELSE {
    WRITE !,"Timestamp is:  ",a
    WRITE !,"ZTimestamp is: ",b }
 
The following example sets the LastUpdate field in the selected row of the Orders table to the current system date and time.
UPDATE Orders SET LastUpdate = CURRENT_TIMESTAMP
  WHERE Orders.OrderNumber=:ord
The following example creates a table named Orders, which records product orders received:
CREATE TABLE Orders (
     OrderId     INT NOT NULL,
     ClientId    INT,
     ItemName    CHAR(40) NOT NULL,
     OrderDate   TIMESTAMP DEFAULT CURRENT_TIMESTAMP(3),
     PRIMARY KEY (OrderId))
The OrderDate column contains the date and time that the order was received. It uses the TIMESTAMP data type and inserts the current system date and time as the default value using the CURRENT_TIMESTAMP function with a precision of 3.
See Also