Date / Time Functions

SQLstream makes a number of date / time / timestamp functions available.

Date / Time functions

The following built-in functions relate to date and time. See Date and Time Patterns for information on date and time formatting for s-Server.

Function Definition
CURRENT_ROW_TIMESTAMP Returns the current timestamp as defined by the computer on which the s-Server is running. CURRENT_ROW_TIMESTAMP is always returned as UTC (GMT, or Coordinated Universal Time), not the local timezone. See section below
CURRENT_DATE Returns the current time when the query executes as defined by the computer on which the SQLstream s-Server is running. Time is in UTC, not the local time zone.
CURRENT_TIME Returns the current time when the query executes as defined by the computer on which the SQLstream s-Server is running. This time is also in UTC, not the local time zone.
CURRENT_TIMESTAMP Returns the current database system timestamp (as defined on the computer on which SQLstream s-Server is running) as a datetime value.
LOCALTIME Returns the current time when the query executes as defined by the computer on which the SQLstream s-Server is running. LOCALTIME is always returned as UTC, not the local timezone.
LOCALTIMESTAMP Returns the current timestamp as defined by the computer on s-Server is running. Time is always returned as UTC (GMT), not the local timezone.

Use CURRENT_ROW_TIMESTAMP in streaming queries

In accordance with the SQL standard, the functions CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, LOCALTIME and LOCALTIMESTAMP all produce the same result for every row in a query. For a short-running finite (database) query this is desirable, but for a streaming query that may run for hours, days or even years, this is usually not what is expected.

in a streaming context, it is more helpful to use the SQLstream function CURRENT_ROW_TIMESTAMP or the SQL expression ROWTIME are the most useful, because they give you information about the times of streaming data as it is processed, not just when the query is started. 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 useful information.

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 s-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 TimeZone on the Oracle web site.

Other Date / Time functions

Function Definition
DATEDIFF Returns the difference between two TIMESTAMP expressions as measured in datePart units
TSDIFF Returns the number of milliseconds between two TIMESTAMP expressions