Dates, Times and Timestamps are represented in different databases in an amazing plethora of manners. Espresso encodes dates, times and timestamps using small subset of ISO-8601 formats.
Dates: yyyy-MM-dd where yyyy is a four-digit year, MM is a 2 digit, leading zero month number from 1 to 12 and dd is a 2 digit, leading zero day of month 1 to 31.
Times: HH:mm:ss(.S*)? where HH is a 24 hour hour time, 00 to 23, mm is the minute from 0 to 59, ss is the seconds from 0 to 59 and .S* represents an optional fractional seconds
Elapsed Time: [+-]?H+:mm:ss(.S*)? where the value represents a positive or negative elapsed time.
Timestamps: yyyy-MM-ddTHH:mm:ss(.S*)?([-+]zz(:?ZZ)?)? where the date and time are as above, and zz:ZZ represent a positve zz hour, ZZ minute offset from UTC (aka GMT)
Different database support different precisions for the various types. For TIMESTAMPS, Espresso always emits up 3 fractional digits for seconds when the precision is 3 or less, and more when the precision is higher.
When the database has more precision, you will need to use a library such as Moment.js or other mechanisms to correctly parse the date.
When Espresso READS dates, times and timestamps from the JSON, it attempts to be forgiving and somewhat lenient. When more values are given than supported by the precision, the given value is ROUNDED to the required precision. As a result of this, timestamps as parsed by Espresso may result in a different value inserted into the database from what a raw native database statement might produce.
You MUST include the property noDatetimeStringSync=true on the URL when using TIME with ANY fractional seconds or TIMESTAMP or DATETIME with more than 3 fractional seconds.
TIME, TIME(0..6) - TIME is equivalent to TIME(0). up to microsecond precision, and can be used to represent a positive or negative elapsed time.
DATETIME, DATETIME(0..6) - DATETIME is equivalent to DATETIME(0) up to microsecond precision.
database default values must use the CURRENT_TIMESTAMP(n) function (note, this is NOT an error, CURRENT_DATETIME is NOT recognized by MySQL)
TIMESTAMP, TIMESTAMP(0..6) - TIMESTAMP is equivalent to TIMESTAMP(6)
limited range of years supports (max 2037)
TIME, TIME(0..7) - TIME is equivalent to TIME(7)
SMALLDATETIME, represents time to the MINUTE, seconds ranging from 00 to 59, that represent the second are rounded such that values that are 29.998 seconds or less are rounded down to the nearest minute, Values of 29.999 seconds or more are rounded up to the nearest minute.
DATETIME - supports 3 digits fractional seconds. SQL Server ROUNDS the fractional seconds to increments of .000, 0.003, 0.007
DATETIME2, DATETIME2(0..7) - DATETIME2 is equivalent to DATETIME2(7). SQL Server TRUNCATES the fractional seconds to the precision supported
DATETIMEOFFSET, DATETIMEOFFSET(0..7) - DATETIMEOFFSET is equivalent to DATETIMEOFFSET(7). SQL Server TRUNCATES the fractional seconds to the precision supported.
Note that SQL Server TIMESTAMP column type is NOT a representation of time, but a SQL Server internal number that changes for each update of a record. This is the same as
the preferred ROWVERSION column type.
Note that for SMALLDATETIME and DATETIME, there is currently a limitation when used as part of a primary key, that the value is the JSON must round to a value as accepted by the database.
Note a current limitation for DATETIMEOFFSET columns is any input value is converted to UTC.
DATE - represents a date/time to the second.
TIMESTAMP, TIMESTAMP(0..9), TIMESTAMP is equivalent to TIMESTAMP(6). date/time up to the nanoseconds.
TIMESTAMP WITH TIME ZONE, TIMESTAMP(0..9) WITH TIME ZONE, default fractional second precision is 6. date/time up to nanoseconds plus time zone representation
TIMESTAMP WITH LOCAL TIME ZONE, TIMESTAMP(0..9) WITH LOCAL TIME ZONE, default fractional second precision is 6.
INTERVAL YEAR TO MONTH, INTERVAL YEAR(0..9) TO MONTH. default precision is 2.
INTERVAL DAY TO SECOND, INTERVAL DAY(0..9) TO SECOND(0..9), default precision is 2 for day and 6 for fractional seconds precision.
Note INTERVAL types are currently not well supported in the current release Please contact us if you use this data type.
DATE - date
TIME WITH TIME ZONE, TIME(0..6) WITH TIME ZONE,
DATETIME, TIMESTAMP, TIMESTAMP(0..6) - time to millisecond precision
TIMESTAMP WITH TIME ZONE, TIMESTAMP(0..6) WITH TIME ZONE
INTERVAL - not currently supported
DATE - a date
TIME, TIME(0..9) - a time of day, TIME Is equivalent to TIME(0)
TIMESTAMP, TIMESTAMP(0..9) - a date and time of day, TIMESTAMP is equivalent to TIMESTAMP(6)
Note, default values may be 'now', 'today', 'yesterday', 'tomorrow' represent the current date/time and midnight (the start of a day) for the today, tomorrow and yesterday values.
DATE - a date
TIME - time accurate to the hundredths of a second (2 digits fractional seconds)
TIMESTAMP - accurate to milliseconds (3 digits fractional seconds)