Date and Time Values in JSON Payloads
Date and Time Values in URL Parameters
Displaying Date and Time Values
UTC Date and Time Usage in IVR Technologies Applications
Regardless of whether it's a date, time or date+time, it will always be returned in the JSON payload in ISO 8601 format as the number of seconds since 1970.
Date/time values in JSON payloads MUST be encoded in ISO 8601 format.
Date/time values in URL parameters (See Below) MUST also be encoded in ISO 8601 format.
For example:"StartTime": "2015-09-01T08:21:43.000Z"
or"StartDate": "2015-09-01"
Empty fields will be encoded by the API as the number of seconds since 1970:"StartTime": "1970-01-01T00:00:00.000Z"
Unless otherwise specified, all date & time values in JSON payloads are in GMT/UTC.
Date & time values passed as URL parameters are in local time and will be converted to UTC internally based on the Users GMT offset.
Unless otherwise specified, date ranges in URL parameters should be passed in the LOCAL TIME of the user's browser.
Example:
GET ivrapi/accounts/3/callsessionlog?StartDate=2016-01-04T23:58:35&EndDate=2016-09-07T23:58:35&sessionsignature=317D97BD&=42620
For column definitions, please see Returning Tabular Data.
If the column definition's field type is:
TIME (5)
Display as HH:MM:SS in 24 hour time
Example:2016-08-31T13:05:47 --> 1:05:47
DATETIME (6)
Display as YYYY-MM-DD HH:MM:SS in 24 hour time
Example:2016-08-31T13:05:47 --> 2016-08-31 1:05:47
DATE (7)
Display as YYYY-MM-DD
Example:2016-08-31T13:05:47 --> 2016-08-31
Sample Result Set:
{
"status": "success",
"message": "",
"data":
[
{
"columns":
[
{
"caption": "Date",
"field": "DATE",
"type": 6, <-----------------------
"visible": true,
"width": 23
},
{
"caption": "Time",
"field": "TIME",
"type": 5, <-----------------------
"visible": true,
"width": 23
}
],
"rows":
[
{
"DATE": "2016-08-31T13:05:47", <-----------------------
"TIME": "2016-08-31T13:05:47", <-----------------------
}
]
}
]
}
In order to provide accurate billing and traffic records for carrier reconciliation and reporting purposes regardless of geographic location, Talking SIP and its supporting applications utilize UTC (Universal Time, Coordinated – also referred to as GMT, or Greenwich Mean Time) for date and time columns in several tables. For more information on UT please see Wikipedia.
The columns that store date/time values as UTC can be found in the schema definition for each table as provided in Table Definitions section.
The following table provides a list of applications with an explanation of how and where UTC is calculated and used and whether or not the application requires the clock to be set accurately on the system where it is running.
Some applications cannot be guaranteed to be running on a system with an accurate clock and therefore must rely upon the clock of the SQL Server machine when inserting rows, whereas other applications require that the system they are running on has a clock that is accurate. Because of this, IT IS CRUCIAL THAT THE CLOCK IS CONSISTENTLY KEPT ACCURATE ON YOUR SQL SERVER MACHINE AND ON THE MACHINE OF ANY IVR APPLICATION THAT REQUIRES AN ACCURATE SYSTEM CLOCK AS NOTED BELOW.
| Application | Requires Accurate System Clock | Tasks Affected | Explanation/Caveats |
|---|---|---|---|
| Talking SIP | Yes | Account Activation and Expiration Creation of Billing and Traffic records. Rating EVENT_LOG table entries. OUTBOUND_REQUESTS table entries. | Talking SIP requires an accurate system clock primarily for activating accounts and generating billing and traffic records. This is to ensure that when billing records are viewed from a system (e.g. the TMC) that the dates and times are displayed based upon the client system’s time zone (assuming the clock is accurate on that system). For Rate Schedules, the date ranges for when a specific Rate Schedule Item is applicable are specified in local time from the Talking SIP machine’s perspective. Account expiration is also determined based on the local time of the Talking SIP machine. |
| Telephony Management Console (i.e. TMC) | No | Account Management (e.g. applying credits/debits to accounts) Customer Support (.e.g. Incident Management) Invoicing* Reporting* | Due to the fact the TMC is a client application, it is typically run on user systems where the date and time is inaccurate. This means that date/time values based on the machine’s local time can never be relied upon for accuracy. Dates and times displayed in the TMC (e.g. billing records) will use the local time of the system it is running on to determine the time zone and convert date/time values accordingly. Therefore, all of the operations that affect tables with UTC date/time columns utilize the clock from the SQL Server machine. This is typically done by using the Transact SQL GetUTCDate() function in insert queries and stored procedures that are performed against the database. *The only exception to this rule is when the user is running reports (including invoice statement reports and when specifying date values like the cut off date when generating invoices), which typically require the user to specify a date range. Since the dates specified are from the user’s perspective (and therefore from the user’s time zone), the assumption is made that the time zone of the user’s machine is accurate. Therefore, the columns in the database that store date/time values as UTC must be converted to the local time zone before being compared to the dates in the range specified by the user. This is done internally by calculating the difference in minutes between the time zone and UTC on the user’s machine (called the Bias) and adding it to the appropriate table columns before comparison to the user’s input dates. |
| Service Charge Server | Yes | All | Since the Service Charge Server runs on a specific schedule and performs time-sensitive queries against the database, having the clock accurate on the machine where it is running is mandatory. Specifically, the time zone bias (difference in minutes between the local time zone and UTC) is passed to several queries to ensure accurate processing of Billing Packages. Note: The date and time values inserted into the WEBCC table for processing by the PGS are specified in local time and are inserted using the current time of the system clock. |
| Payment Gateway Server (PGS) | Yes | Account signup/updates. Creation of billing records. | The PGS requires an accurate system clock primarily for creating/updating accounts and generating billing records. |
| License Manager | No | N/A | N/A |
| End User Web Interface | Yes | N/A | N/A |