Date and Time

Date and Time
calac41
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:
Format
Representation in API Creator
Dates
 
yyyy-MM-dd
 
where 
yyyy
 is a four-digit year, 
MM
 is a 2 digit, leading-zero month number from 1 through 12, and 
dd
 is a 2 digit, leading-zero day of month 1 through 31.
Times
 
HH:mm:ss(.S*)?
 
where 
HH
 is a 24-hour hour time from 00 through 23, 
mm
 is the minute from 0 to 59, 
ss
 is the seconds from 0 through 59, and 
.S*
 represents 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 noted previously in this table, 
zz:ZZ
 represent a positive 
zz
 hour, and 
ZZ
 minute offset from UTC (aka GMT).
Each database supports different precisions for the various types. For TIMESTAMPS, API Creator emits up three fractional digits for seconds when the precision is three or less, and more when the precision is higher. This is done as some JavaScript is unable to correctly parse timestamps without three fractional digits for seconds. When the database has more precision, correctly parse the date using a library such as 
Moment.js
 or 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:
 
 
Database Specific
MySQL
  • When you use TIME with any fractional seconds or TIMESTAMP or DATETIME with more than three 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.
    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.
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
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)