TIMESTAMP

The TIMESTAMP data type represents a combined date and time value. Splice Machine stores timestamp values with up to 6 fractional second digits in databases, and allows you to specify literal timestamp values with up to 9 fractional second digits.

Splice Machine displays TIME and TIMESTAMP values using the current time zone for the server to which you are connected. DATE values, however, are stored as fixed strings, and are not affected by the time zone of the server to which you are connected.

Syntax

TIMESTAMP

Corresponding Compile-time Java Type

java.sql.Timestamp

JDBC Metadata Type (java.sql.Types)

TIMESTAMP

About Timestamp Formats

Splice Machine uses the following Java date and time pattern letters to construct timestamps:

Pattern Letter Description Format(s)
y year yy or yyyy
M month MM
d day in month dd
h hour (0-12) hh
H hour (0-23) HH
m minute in hour mm
s seconds ss
S tenths of seconds SSS (up to 6 decimal digits: SSSSSS)
z time zone text e.g. Pacific Standard time
Z time zone, time offset e.g. -0800

The default timestamp format for Splice Machine imports is: yyyy-MM-dd HH:mm:ss, which uses a 24-hour clock, does not allow for decimal digits of seconds, and does not allow for time zone specification.

Please see Working With Date and Time Values for information about using simple arithmetic with TIMESTAMP values.

Examples

The following tables shows valid examples of timestamps and their corresponding format (parsing) patterns:

Timestamp value Format Pattern Notes
2013-03-23 09:45:00 yyyy-MM-dd HH:mm:ss This is the default pattern.
2013-03-23 19:45:00.98-05 yyyy-MM-dd HH:mm:ss.SSZ This pattern allows up to 2 decimal digits of seconds, and requires a time zone specification.
2013-03-23 09:45:00-07 yyyy-MM-dd HH:mm:ssZ This patterns requires a time zone specification, but does not allow for decimal digits of seconds.
2013-03-23 19:45:00.98-0530 yyyy-MM-dd HH:mm:ss.SSZ This pattern allows up to 2 decimal digits of seconds, and requires a time zone specification.

2013-03-23 19:45:00.123

2013-03-23 19:45:00.12

yyyy-MM-dd HH:mm:ss.SSS

This pattern allows up to 3 decimal digits of seconds, but does not allow a time zone specification.

Note that if your data specifies more than 3 decimal digits of seconds, an error occurs.

2013-03-23 19:45:00.1298 yyyy-MM-dd HH:mm:ss.SSSS This pattern allows up to 4 decimal digits of seconds, but does not allow a time zone specification.

Usage Notes

Dates, times, and timestamps cannot be mixed with one another in expressions.

Splice Machine also accepts strings in the locale specific datetime format, using the time zone for the server to which you are connected. If there is an ambiguity, the built-in formats shown above take precedence.

TimeStamp values range from ’01 Jan 0001 00:00:00 GMT’    to    ‘31 Dec 9999 23:59:59 GMT’.

See Also