Date and time fields
Basic types
The Business NXT data model has featured fields that represent either dates or time values. These are stored in the database as integer values with some specific formats, as described in the following table:
| Type | Format | Examples |
|---|---|---|
| date | yyyymmdd |
20190101 for 1 January 2019 |
| time | hhmm |
0 for 00:00, 101 for 01:01, 2330 for 23:30 |
| precision time | (((h * 60 + m) * 60) + s) * 1000 + ms |
0 for 00:00:00.000, 3661001 for 01:01:01.001, 2862000500 for 23:30:30.500 |
Since October 2024, all tables contain two new timestamp fields for created and changed date-time values. These are T-SQL datetime2 values (which in GraphQL are represented as DateTime values).
| Type | Format | Examples |
|---|---|---|
| timestamp | yyyy-MM-dd HH:mm:ss[.nnnnnnn] |
2024-10-01 12:45:33.798 for 1 October 2024, 12:45:33.798 |
Tip
These fields, createdTimestamp and changedTimestamp, should be preferred for use in filters due to their higher precision.
However, these fields are not available for already existing rows, only for new or modified rows after the release date.
Tip
The date/time values represent the local time of the server. They do not indicate the timezone.
Since the GraphQL API exposes the data model as it is internally defined, the date and time fields appear in the API as integers. Here is an example:
query read($cid : Int!)
{
useCompany(no: $cid)
{
order(
first : 2,
filter : {orderDate : {_gt : 20150101}}
)
{
items
{
orderNo
orderDate
createdDate
createdTime
changedDate
changedTime
}
}
}
}
{
"data": {
"useCompany": {
"order": {
"items": [
{
"orderNo": 75,
"orderDate": 20150115,
"createdDate": 20200511,
"createdTime": 1325,
"changedDate": 20200511,
"changedTime": 1325,
"changeTimeInMs": 48308716
},
{
"orderNo": 76,
"orderDate": 20150115,
"createdDate": 20200511,
"createdTime": 1325,
"changedDate": 20200511,
"changedTime": 1325,
"changeTimeInMs": 48308716
}
]
}
}
}
}
The use of the date and time fields require conversions as follow:
For date:
value = 20210122
year = value / 10000
value = value % 10000
month = value / 100
day = value % 100
value = year \* 10000 + month \* 100 + day
For time:
value = 1245
hour = value / 100
minute = value % 100
value = hour \* 100 + minute
For precision time:
value = 2862000500
ms = value % 1000
timeValue = value / 1000
hour = timeValue / 3600
timeValue = timeValue % 3600
minute = timeValue / 60
second = timeValue % 60
value = (((hour \* 60 + minute) \* 60) + second) \* 1000 + ms
The changeTimeInMs field is the only precision time field. However, this is only available on a limited number of tables, listed below:
AssociateAssociateReferenceAssociateInformationAppointmentResourceProductBarcodePriceAndDiscountMatrixOrderOrderLineIncomingDocumentChange
On the other hand, the new timestamp fields apear as fields of the DateTime type, as shown in the following example:
query read_orders($cid : Int!)
{
useCompany(no: $cid)
{
order(
first : 2,
orderBy : {changedTimestamp : DESC})
{
items
{
orderNo
orderType
customerNo
changedDate
changedTime
changedDateTime
changedTimestamp
createdDate
createdTime
createdDateTime
createdTimestamp
}
}
}
}
{
"data": {
"useCompany": {
"order": {
"items": [
{
"orderNo": 4656,
"orderType": 6,
"customerNo": 10002,
"changedDate": 20241010,
"changedTime": 1022,
"changedDateTime": "2024-10-10T10:22:30.399",
"changedTimestamp": "2024-10-10T10:22:30.399",
"createdDate": 20241010,
"createdTime": 1022,
"createdDateTime": "2024-10-10T10:22:00",
"createdTimestamp": "2024-10-10T10:22:29.958"
},
{
"orderNo": 1,
"orderType": 2,
"customerNo": 10002,
"changedDate": 20240207,
"changedTime": 813,
"changedDateTime": "2024-02-07T08:13:14.764",
"changedTimestamp": null,
"createdDate": 20240217,
"createdTime": 1325,
"createdDateTime": "2024-02-17T13:25:00",
"createdTimestamp": null
}
]
}
}
}
}
Date fields and time fields
In order to make it easier to work with date and time values, GraphQL is exposing all these fields also in an ISO date format and, respectively, a time format, according to the following table:
| Type | Format | Examples |
|---|---|---|
| date | yyyy-mm-dd |
2019-01-01, 2021-12-31 |
| time | hh:mm |
00:00, 01:01, 23:30 |
| precision time | hh:mm:ss.ms |
00:00:00.000, 01:01:01.001, 23:30:30.500 |
To make this possible, every date or time field has a companion with the same name, but the suffix AsDate for dates and, respectively, AsTime for time. This is exemplified below:
| Field | Type | Value |
|---|---|---|
orderDate |
integer | 20211022 |
orderDateAsDate |
date | 2021-10-22 |
estimatedTime |
integer | 1710 |
estimatedTimeAsTime |
string | "17:10" |
changeTimeInMs |
integer | 48308716 |
changeTimeInMsAsTime |
string | "13:25:08.716" |
Note: There is no natural date type to represent a time value without a date. Therefore, the type of the time fields with the AsTime suffix is actually string.
These date and time fields are available for:
- connection types (used for reading data)
- aggregate types (only for the
minimumandmaximumaggregate functions) - input types (used with mutations for inserting or updating records)
- filter types (used for filtering records)
Several examples are provided below.
Example: reading data from the system.
query read($cid : Int!)
{
useCompany(no: $cid)
{
generalLedgerAccount(first: 2)
{
items {
accountNo
name
changedDate
changedDateAsDate
changedTime
changedTimeAsTime
}
}
}
}
{
"data": {
"useCompany": {
"generalLedgerAccount": {
"items": [
{
"accountNo": 1000,
"name": "Forskning og utvikling",
"changedDate": 20210218,
"changedDateAsDate": "2021-02-18",
"changedTime": 1710,
"changedTimeAsTime": "17:10"
},
{
"accountNo": 1020,
"name": "Konsesjoner",
"changedDate": 20210219,
"changedDateAsDate": "2021-02-19",
"changedTime": 1020,
"changedTimeAsTime": "10:20"
}
]
}
}
}
}
Example: filtering data.
query read($cid : Int!)
{
useCompany(no: $cid)
{
order(
first : 2,
filter : {
orderDateAsDate : {_gt : "2015-01-01"}
}
)
{
totalCount
items {
orderNo
orderDate
orderDateAsDate
}
}
}
}
{
"data": {
"useCompany": {
"order": {
"totalCount": 268,
"items": [
{
"orderNo": 75,
"orderDate": 20150115,
"orderDateAsDate": "2015-01-15"
},
{
"orderNo": 76,
"orderDate": 20150116,
"orderDateAsDate": "2015-01-16"
}
]
}
}
}
}
Example: input values in insert and update mutations.
mutation create($cid : Int!)
{
useCompany(no: $cid)
{
order_create(values :[{
orderNo : 999,
orderDateAsDate : "2021-10-25"
}])
{
affectedRows
items
{
orderNo
orderDate
orderDateAsDate
}
}
}
}
{
"data": {
"useCompany": {
"order_create": {
"affectedRows": 1,
"items": [
{
"orderNo": 999,
"orderDate": 20211025,
"orderDateAsDate": "2021-10-25"
}
]
}
}
}
}
Example: computing aggregates minimum and maximum.
query read($cid : Int!)
{
useCompany(no: $cid)
{
order_aggregate
{
minimum
{
orderDate
orderDateAsDate
changedTime
changedTimeAsTime
}
maximum
{
orderDate
orderDateAsDate
changedTime
changedTimeAsTime
}
}
}
}
{
"data": {
"useCompany": {
"order_aggregate": {
"minimum": {
"orderDate": 20130105,
"orderDateAsDate": "2013-01-05",
"changedTime": 901,
"changedTimeAsTime": "09:01"
},
"maximum": {
"orderDate": 20151205,
"orderDateAsDate": "2015-12-05",
"changedTime": 1945,
"changedTimeAsTime": "15:45"
}
}
}
}
}
Datetime special fields
Every Business NXT table contains the following fields:
| Field | Type | Description |
|---|---|---|
createdTimestamp |
datetime | The time point when the record was created (e.g. 2021-10-12 13:24:54.165). |
createdDate |
integer | The date when the record was created (e.g. 20211012). |
createdTime |
integer | The time when the record was created (e.g. 1324). |
createdUser |
integer | The ID of the user that created the record. |
changedTimestamp |
datetime | The time point when the record was changed (e.g. 2021-10-12 09:02:13.843). |
changedDate |
integer | The date when the record was last changed (e.g. 20211023). |
changedTime |
integer | The time when the recod was last changed (e.g. 902). |
changedUser |
integer | The ID of the user that made the last change to the record. |
Tip
For precise values, prefer to use the createdTimestamp and changedTimestamp fields. These fields are available for all tables and have a higher precision than the date and time fields.
If these fields are null then you need to use createdDate/createdTime and changedDate/changedTime, respectively.
The pair createdDate/createdTime represents the point in time when the record was created. Similarly, the pair changedDate/changedTime represent the point in time when the record was last changed. As previously mentioned, some tables have another field called changeTimeInMs that includes seconds and miliseconds to the time. These are important in different contexts, such as fetching data created or changed after a particular moment in time.
In order to simplify the use of these date-time values, the API makes these two pairs available through a compound field, as described in the following table:
| Field | Type | Examples |
|---|---|---|
createdDateTime |
datetime | 2021-10-21T13:20:00 |
changedDateTime |
datetime | 2021-10-22T14:59:00 or 2021-10-22T14:59:22.456 (where changeTimeInMs is available) |
These values do not indicate the timezone (as previously mentioned). They represent the local time of the server. A companion set of fields suffixed with Utc are available, which represent the same date-time values, but in the Coordinated Universal Time (UTC) timezone.
| Field | Type | Examples |
|---|---|---|
createdDateTimeUtc |
datetime | 2021-10-21T13:20:00Z |
changedDateTimeUtc |
datetime | 2021-10-22T14:59:00Z or 2021-10-22T14:59:22.456Z (where changeTimeInMs is available) |
In the following example, the changedDateTime is used to select all the general ledger accounts that have been modified since 2021-10-01 17:00:00.
query read($cid : Int!)
{
useCompany(no: $cid)
{
generalLedgerAccount(filter : {
changedDateTime : {
_gte: "2021-10-01T17:00:00"}
})
{
totalCount
items
{
accountNo
name
createdDateTime
createdDateTimeUtc
changedDateTime
changedDateTimeUtc
}
}
}
}
{
"data": {
"useCompany": {
"generalLedgerAccount": {
"totalCount": 2,
"items": [
{
"accountNo": 9001,
"name": "Special ACC",
"createdDateTime": "2021-10-21T13:20:00",
"createdDateTimeUtc": "2021-10-21T13:20:00Z",
"changedDateTime": "2021-10-21T13:21:00",
"changedDateTimeUtc": "2021-10-21T13:21:00Z"
},
{
"accountNo": 9002,
"name": "Extra ACC",
"createdDateTime": "2021-10-21T13:20:00",
"createdDateTimeUtc": "2021-10-21T13:20:00Z",
"changedDateTime": "2021-10-21T13:21:00",
"changedDateTimeUtc": "2021-10-21T13:21:00Z"
}
]
}
}
}
}
The use of the changedDateTime or createdDateTime field is equivalent to the use of an expression built with the changedDate/changedTime or changedDate/changeTimeInMs fields (when available) or, respectively, the createdDate/createdTime fields. Alternatively, the changedDateAsDate/changedTimeAsTime and the createdDateAsDate/createdTimeAsTime fields can be used.
Expression:
changedDateTime OP 2021-10-21T13:20:00
Equivalent to:
(changedDate OP 20211021) OR (changedDate == 20211021 AND changedTime OP 1320)
or
(changedDateAsDate OP "2021-10-21") OR (changedDateAsDate == "2021-10-21" AND changedTimeAsTime OP "13:20")
Expression (where changeTimeInMs exists):
changedDateTime OP 2021-10-21T13:20:10.500
Equivalent to:
(changedDate OP 20211021) OR (changedDate == 20211021 AND changeTimeInMs OP 48010500)
or
(changedDateAsDate OP "2021-10-21") OR (changedDateAsDate == "2021-10-21" AND changedTimeInMsAsTime OP "13:20:10.500")
Where OP is _gt, _gte, _lt, lte.
An example for the filter expression changedDateTime >= "2021-10-01T07:00:00" is provided below:
query read($cid : Int!)
{
useCompany(no: $cid)
{
generalLedgerAccount(filter : {
changedDateTime : {
_gte : "2021-10-01T07:00:00"},
})
{
totalCount
items
{
accountNo
name
createdDateTime
changedDateTime
}
}
}
}
query read($cid : Int!)
{
useCompany(no: $cid)
{
generalLedgerAccount(filter : {
_or : [
{changedDate : {_gt : 20211001}},
{
_and : [
{changedDate : {_eq : 20211001}},
{changedTime : {_gte : 700}}
]
}
]
})
{
totalCount
items
{
accountNo
name
createdDateTime
changedDateTime
}
}
}
}
To filter with a date time value that includes second and milliseconds the query and its equivalent are as follows (example for changedDateTime >= "2023-05-16T07:17:28.659"):
query read($cid : Int!)
{
useCompany(no: $cid)
{
order(
filter : {
changedDateTime : {
_gte : "2023-05-16T07:17:28.659"}
}
)
{
totalCount
items
{
orderNo
orderDate
changedDateTime
}
}
}
}
query read($cid : Int!)
{
useCompany(no: $cid)
{
order(filter : {
_or : [
{changedDate : {_gt : 20230516}},
{
_and : [
{changedDate : {_eq : 20230516}},
{changeTimeInMs : {_gte : 26248659}}
]
}
]
})
{
totalCount
items
{
orderNo
orderDate
changedDateTime
}
}
}
}
The datetime format has the ISO8601 compliant form yyyy-mm-ddTHH:mm:ss.ms, such as 2021-10-01T07:00:00 and 2023-05-16T07:17:28.659 in the examples above.
In the case of filter objects, the changedDateTime and createdDateTime fields have a companion field called changedDateTimeTZ and createdDateTimeTZ, respectively. The values for thse two fields is expected to be in a specific timezone. Therefore, it will be converted to the local time before being compared with date and time values in the database.
query read($cid : Int!)
{
useCompany(no: $cid)
{
order(
filter : {
changedDateTimeTZ : {
_gte : "2023-05-16T09:17:28.659+02:00"}
}
)
{
totalCount
items
{
orderNo
orderDate
changedDateTimeUtc
}
}
}
}
The Business NXT time fields to not store seconds, only hours and minutes. For the tables that have the changeTimeInMs field available, seconds and milliseconds are also available. However, for the majority of tables, this field is not present. Regardless the case, when you use the createdDateTime and changedDateTime fields and specify a date-time value, you must also supply a value for seconds. Failure to do so, such as in the example 2021-10-01T07:00 will result in the following GraphQL error:
{
"errors": [
{
"message": "Argument 'filter' has invalid value. In field 'changedDateTime': [In field '_gte': [Expected type 'DateTime', found \"2021-10-01T07:00\".]]",
"locations": [
{
"line": 5,
"column": 26
}
],
"extensions": {
"code": "ARGUMENTS_OF_CORRECT_TYPE",
"codes": [
"ARGUMENTS_OF_CORRECT_TYPE"
],
"number": "5.6.1"
}
}
]
}