Date and Time

Dates, times, and timestamps are represented in different databases in a 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 databases support 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 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, the timestamps API Creator parses can result in a different value inserted into the database from what a raw native database statement might produce.

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.
    Note: 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.

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)