Table of Contents

Search

  1. Preface
  2. The Transformation Language
  3. Constants
  4. Operators
  5. Variables
  6. Dates
  7. Functions
  8. Creating Custom Functions
  9. Custom Function API Reference

Transformation Language Reference

Transformation Language Reference

SYSTIMESTAMP

SYSTIMESTAMP

Returns the current date and time of the node hosting the
PowerCenter 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
    PowerCenter Integration Service
    evaluates the function for each row in the transformation.
  • When you configure the argument of SYSTIMESTAMP as a constant, the
    PowerCenter 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
PowerCenter 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