Time Functions

The following built-in functions relate to time. See date and time formats below 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.
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_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_TIMESTAMP Returns the current database system timestamp (as defined on the computer on which SQLstream s-Server is running) as a datetime value.
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.
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.
UNIX_TIMESTAMP Converts a timestamp to a Unix timestamp (milliseconds since ‘1970-01-01 00:00:00’ UTC) as a BIGINT.
TO_TIMESTAMP Returns a representation of the UNIX_TIMESTAMP argument as a value in ‘YYYY-MM-DD HH:MM:SS’ format. The value is expressed in the current time zone. unix_timestamp is an internal timestamp value such as is produced by the UNIX_TIMESTAMP function.

Of these, the SQL extension CURRENT_ROW_TIMESTAMP and the SQL expression ROWTIME (/sql-reference-guide/select-statement/rowtime-column/) 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.

Date and Time Patterns

date and Time Patterns See http://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html for more detail. You specify date and time formats using patterned letters. Date and time pattern strings use unquoted letters from ‘A’ to ‘Z’ and from ‘a’ to ‘z’, with each letter representing a formatting element.

Note: if you include other characters, they will be incorporated into the output string during formatting or compared to the input string during parsing.

The following pattern letters are defined (all other characters from ‘A’ to ‘Z’ and from ‘a’ to ‘z’ are reserved):

page.

Date or Time Component Pattern Letter Presentation as text or number Examples
Era designator G Text AD
Year y Year 1996; 96|
Month in year M Month July; Jul; 07
Week in year w Number 27
Week in month W Number 2
Day in year D Number 189
Day in month d Number 10
Day of week in month F Number 2
Day in week E Text EE=Tu; EEE=Tue; EEEE=Tuesday
Am/pm marker a Text PM
Hour in day (0-23) H Number 0
Hour in day (1-24) k Number 24
Hour in am/pm (0-11) K Number 0
Hour in am/pm (1-12) h Number 12
Minute in hour m Number 30
Second in minute s Number 55
Millisecond S Number 978
Time zone z General Pacific Standard Time; PST; GMT-08:00
Time zone Z RFC -0800

Pattern letters are usually repeated, as their number determines the exact presentation:

Text

For formatting, if the number of pattern letters is 4 or more, the full form is used; otherwise a short or abbreviated form is used if available. For parsing, both forms are accepted, independent of the number of pattern letters.

Number

For formatting, the number of pattern letters is the minimum number of digits, and shorter numbers are zero-padded to this amount. For parsing, the number of pattern letters is ignored unless it’s needed to separate two adjacent fields.

Year

For formatting, if the number of pattern letters is 2, the year is truncated to 2 digits; otherwise it is interpreted as a number

For parsing, if the number of pattern letters is more than 2, the year is interpreted literally, regardless of the number of digits. So using the pattern “MM/dd/yyyy”, “01/11/12” parses to Jan 11, 12 A.D.

For parsing with the abbreviated year pattern (“y” or “yy”), the formatting codes and rules are the same as for the SimpleDateFormat java class: the abbreviated year is interpreted relative to some century by adjusting dates to be within 80 years before and 20 years after the time the SimpleDateFormat instance is created. For example, using a pattern of “MM/dd/yy” and a SimpleDateFormat instance created on Jan 1, 2018, the string “01/11/12” would be interpreted as Jan 11, 2012 while the string “05/04/64” would be interpreted as May 4, 1964.

During parsing, only strings consisting of exactly two digits, as defined by Character.isDigit(char ch), will be parsed into the default century. Any other numeric string, such as a one digit string, a three or more digit string, or a two digit string that isn’t all digits (for example, “-1”), is interpreted literally. So “01/02/3” or “01/02/003” are parsed, using the same pattern, as Jan 2, 3 AD. Likewise, “01/02/-3” is parsed as Jan 2, 4 BC.

Month

If the number of pattern letters is 3 or more, the month is interpreted as text. Otherwise, it is interpreted as a number.

General Time Zone

Time zones are interpreted as text if they have names. For time zones representing a GMT offset value, the following syntax is used:

GMTOffsetTimeZone:
GMT Sign Hours : Minutes
Sign: one of
\+ -
Hours:
Digit
Digit Digit
Minutes:
Digit Digit
Digit: one of
0 1 2 3 4 5 6 7 8 9

Hours must be between 0 and 23, and Minutes must be between 00 and 59. The format is locale independent and digits must be taken from the Basic Latin block of the Unicode standard.

For parsing, RFC 822 time zones are also accepted.

RFC 822 time zone

For formatting, the RFC 822 4-digit time zone format is used:

RFC822TimeZone:
Sign TwoDigitHours Minutes
TwoDigitHours:
Digit Digit

TwoDigitHours must be between 00 and 23. Other definitions are as for general time zones.

For parsing, general time zones are also accepted.

SimpleDateFormat also supports “localized date and time pattern” strings. In these strings, the pattern letters described above may be replaced with other, locale dependent, pattern letters. SimpleDateFormat does not deal with the localization of text other than the pattern letters; that’s up to the client of the class.

Examples

The following examples show how date and time patterns are interpreted in the U.S. locale. The given date and time are 2001-07-04 12:08:56 local time in the U.S. Pacific Time time zone.

Date and Time Pattern Result
“yyyy.MM.dd G ‘at’ HH:mm:ss z” 2001.07.04 AD at 12:08:56 PDT
“EEE, MMM d, “yy” Wed, Jul 4, ‘01
“h:mm a” 12:08 PM
“hh ‘o”clock’ a, zzzz” 12 o’clock PM, Pacific Daylight Time
“K:mm a, z” 0:08 PM, PDT
“yyyyy.MMMMM.dd GGG hh:mm aaa” 02001.July.04 AD 12:08 PM
“EEE, d MMM yyyy HH:mm:ss Z” Wed, 4 Jul 2001 12:08:56 -0700
“yyMMddHHmmssZ” 010704120856-0700
“yyyy-MM-dd’T’HH:mm:ss.SSSZ” 2001-07-04T12:08:56.235-0700

Date and time pattern letters in alphabetic order

The same pattern letters shown at first, above, in Date or Time Component order are shown below in alphabetic order for easy reference.

Pattern Letter Date or Time Component Presentation as text or number Examples
a Am/pm marker Text PM
D Day in year Number 189
d Day in month Number 10
E Day in week Text EE=Tu; EEE=Tue; EEEE=Tuesday
F Day of week in month Number 2
G Era designator Text AD
H Hour in day (0-23) Number 0
h Hour in am/pm (1-12) Number 12
k Hour in day (1-24) Number 24
K Hour in am/pm (0-11) Number 0
M Month in year Month July; Jul; 07
m Minute in hour Number 30
s Second in minute Number 55
S Millisecond Number 978
w Week in year Number 27
W Week in month Number 2
y Year Year 1996; 96
z Time zone General Pacific Standard Time; PST; GMT-08:00 |
Z Time zone RFC -0800