SYSTIMESTAMP
Returns the current date and time of the node hosting the Data Integration Service with precision to the nanosecond. The precision to which you display the date and time depends on the platform.
The return value of the function varies depending on how you configure the argument:
- •When you configure the argument of SYSTIMESTAMP as a variable, the Data Integration Service evaluates the function for each row in the transformation.
- •When you configure the argument of SYSTIMESTAMP as a constant, the Data Integration Service evaluates the function once and retains the value for each row in the transformation.
Syntax
SYSTIMESTAMP( [format] )
The following table describes the argument for this command:
Argument | Required/ Optional | Description |
|---|
format | Optional | Precision to which you want to retrieve the timestamp. You can specify precision up to seconds (SS), milliseconds (MS), microseconds (US), or nanoseconds (NS). Enclose the format string within single quotation marks. The format string is not case sensitive. For example, to display the date and time to the precision of milliseconds use the following syntax: SYSTIMESTAMP(‘MS’). Default precision is microseconds (US). |
Return Value
Timestamp. Returns date and time to the specified precision.
Examples
Your organization has an online order service and processes real-time data. You can use the SYSTIMESTAMP function to generate a primary key for each transaction in the target database.
Create an Expression transformation with the following ports and values:
Port Name | Port Type | Expression |
|---|
Customer_Name | Input | n/a |
Order_Qty | Input | n/a |
Time_Counter | Variable | ‘US’ |
Transaction_Id | Output | SYSTIMESTAMP ( Time_Counter ) |
At run time, the Data Integration Service generates the system time to the precision of microseconds for each row:
Customer_Name | Order_Qty | Transaction_Id |
|---|
Vani Deed | 14 | 07/06/2007 18:00:30.701015000 |
Kalia Crop | 3 | 07/06/2007 18:00:30.701029000 |
Vani Deed | 6 | 07/06/2007 18:00:30.701039000 |
Harry Spoon | 32 | 07/06/2007 18:00:30.701048000 |