Time Functions

<< Click to Display Table of Contents >>

Navigation:  Streaming SQL Reference Guide > Basic Building Blocks > Functions > Scalar Functions >

Time Functions

Previous pageReturn to chapter overviewNext page

The following built-in functions relate to time.

CURRENT_ROW_TIMESTAMP
ROWTIME
CURRENT_TIME
CURRENT_DATE
CURRENT_TIMESTAMP
LOCALTIMESTAMP
LOCALTIME

Of these, the SQL extension CURRENT_ROW_TIMESTAMP and the SQL expression ROWTIME are the most useful for a streaming context, because they give you information about the times of streaming data as it emerges, not just when the query is run. This is a key difference between a streaming query and a traditional RDMS query: streaming queries remain "open," producing more data, so the timestamp for when the query was run does not offer good information.

LOCALTIMESTAMP, LOCALTIME, CURRENT_DATE, and CURRENT_TIMESTAMP all produce results which are set to values at the time the query first executes. Only CURRENT_ROW_TIMESTAMP generates a row with a unique timestamp (date and time) for each row.

A query run with LOCALTIMESTAMP (or CURRENT_TIMESTAMP or CURRENT_TIME) as one of the columns puts into all output rows the time the query is first run. If that column instead contains CURRENT_ROW_TIMESTAMP, each output row gets a newly-calculated value of TIME representing when that row was output.

Time zones

For a variety of reasons related to syncing streams, it is best practice to run s-Server in UTC. The server is set to run in UTC by default. Unless all agents will be in the same time zone as s-Server, it is preferable to keep s-Server set to UTC.

By default, all time functions return time in UTC (GMT), including CURRENT_ROW_TIMESTAMP. If the server has been set to run in a local time zone, then both LOCALTIMESTAMP and LOCALTIME will return values in the local time zone. Otherwise, LOCAL and CURRENT values will be the same.

Time and JDBC

Time data accessed via JDBC are accessed as Java timestamp values and follow Java data semantics. A Java java.sql.Timestamp object contains a long (64 bit signed integer) field that represents the number of milliseconds since January 1st, 1970 UTC. (For example, on any particular day, 6:00AM PST and 9:00AM EST are the same moment, and both correspond to the same java.sql.Timestamp.)

The tension between Java and UTC is resolved by default when s-Server runs with UTC as the timezone (again, this is the default mode for s-Server). This allows JDBC clients to remain in their local timezone. If you read/write data using the JDBC setTimestamp(Timestamp) and getTimestamp() methods, timestamp values will automatically be converted (by the java runtime library) to UTC timestamp values. An alternative design is for the JDBC client to locate itself in the UTC zone. In this case the java library does no conversion. For more information, see the topic Time Zone on the Oracle web site.