Date and Time
Date and Time
API Creator uses the following methods to represent dates, times, and timestamps for the various databases. API Creator encodes dates, times, and timestamps using small subset of ISO-8601 formats:
Representation in API Creator
yyyyis a four-digit year,
MMis a 2 digit, leading-zero month number from 1 through 12, and
ddis a 2 digit, leading-zero day of month 1 through 31.
HHis a 24-hour hour time from 00 through 23,
mmis the minute from 0 to 59,
ssis the seconds from 0 through 59, and
.S*represents optional fractional seconds.
where the value represents a positive or negative elapsed time.
where the date and time are as noted previously in this table,
zz:ZZrepresent a positive
ZZminute offset from UTC (aka GMT).
Moment.jsor other mechanisms.
When API Creator READS dates, times, and timestamps from the JSON, it attempts to be forgiving and 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, the timestamps API Creator parses can result in a different value inserted into the database from what a raw native database statement might produce.
In this article:
- When you use TIME with any fractional seconds or TIMESTAMP or DATETIME with more than three fractional seconds, include thenoDatetimeStringSync=trueproperty 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 theCURRENT_TIMESTAMP(n)function.This is not an error. MySQL does not recognize CURRENT_DATETIME.
- TIMESTAMP, TIMESTAMP(0..6) - TIMESTAMP is equivalent to TIMESTAMP(6) limited range of years supports (max 2037)
Microsoft SQL Server
- 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 three 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.
- 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.
- 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.
- 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
- DATE - a date
- TIME - time accurate to the hundredths of a second (2 digits fractional seconds)
- TIMESTAMP - accurate to milliseconds (3 digits fractional seconds)