Date and Time

Dates, times, and timestamps are represented in different databases in an amazing plethora of manners. API Creator 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, API Creator emits up three fractional digits for seconds when the precision is 3 or less, and more when the precision is higher.
This is done as some JavaScript are unable to correctly parse timestamps without 3 fractional digits for seconds.

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 API Creator 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 API Creator may result in a different value inserted into the database from what a raw native database statement might produce.

Database Specific

MySQL

When you are using TIME with ANY fractional seconds or TIMESTAMP or DATETIME with more than 3 fractional seconds, include the noDatetimeStringSync=true property on the URL.

DATETIME, 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)

SQLServer

TIME, TIME(0..7) - TIME is equivalent to TIME(7) DATE 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:
  • 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.
  • There is a limitation when using SMALLDATETIME and DATETIME as part of a primary key. The value is the JSON must round to a value as accepted by the database.
  • There is a limitation for DATETIMEOFFSET columns is any input value is converted to UTC.
Oracle

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.

PostgreSQL

DATE - date

TIME, TIME(0..6), 

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

NuoDB

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.

Pervasive SQL

DATE - a date

TIME - time accurate to the hundredths of a second (2 digits fractional seconds)

TIMESTAMP - accurate to milliseconds (3 digits fractional seconds)
Comments