RecordPoint Dates

Good software design principles dictate that all dates and times should be stored in the back-end in a UTC DateTime format. There are multiple reasons for doing this, largely to do with working across multiple time-zones. This, for most software design, this is easy as end user’s rarely get to see raw back-end data. However, due to the nature of Records Management, this is not always the case. The way dates and times are implemented in RecordPoint is described.

Lets consider an example 9am on January 1^st 2017 in Sydney (Australian Eastern Daylight Time UTC + 11:00). When RecordPoint gets a time as follows, it is converted to the same instant in UTC format. This is ‘2016-12-31T22:00:00.0000000Z’. This time looks like it is the day before. However, this is the same instant in time.

This time is what is stored in the backend. RecordPoint will then convert this time back to local time before using or displaying it. RecordPoint will use either the site’s regional settings for this or the client machine’s time zone settings depending on the context. The former is usually used when RecordPoint needs to do calculations (such as for retention/disposition) and the latter is usually used for displaying in the UI.

Displaying in the UI

When displaying dates in the UI, end users will usually see a short date format which can be specified in the site’s locale. This can be found in Site Settings -> Regional settings. For example, if the Locale is English (United States), the date format will be MM/DD/YYYY. Times will be shown in the same way, although they are shown in the UI less often.

There are some exceptions to this which include Search, “View Metadata” and Custom Columns. These are outlined in following sections.

Editing in the UI

When editing dates, a date picker will usually be present. When a date is inserted into the picker, the RecordPoint client will interpret this as midnight of that day (start of day) before converting this to UTC for the back-end accordingly.

When doing so, the dates are converted as per the time conversion for that day. For example, when Sydney is on EADT (UTC+11:00), a date selected in the date-picker will be stored in the back-end as 1pm the day before. Likewise, when Sydney is on EAST (UTC +10:00), the date will be 2pm the day before.

Viewing Metadata

This page shows the raw data in the storage layer. This is useful for creating rules, matching raw metadata patterns and also for RecordPoint Support to help with customer concerns. As such, it shows the raw UTC format which may look like an incorrect time, despite being the same instant in time.

Searching

Search in RecordPoint is split into 2 different parts. There is Advanced Search which uses standard SharePoint Search and there is Record/File and Box search. When using dates, we recommend using the Record/File/Box search for reasons following.

In Advanced Search, there is no date-picker, so, when finding records with a certain piece of DateTime metadata, users will need to use the UTC format. This limiting as, to find records from a certain date, a records manager might have to do a search on the date required and either the day before or after depending on which side of the date line they are based. For example, a search for a Sydney based company would need to search on the current day, as well as the day before as any time up to 11am could be converted to the day before. Even then, the search might return records which are from the actual day before. This is not an elegant solution. However, there is a better approach.

Go to Record/File/Box search and add a date search property. Because this page does not leverage SharePoint search, we are able to add in our own date-picker. This means that the search query is built by the RecordPoint client and the search is done in local time. A search on todays records will then produce the correct result. Furthermore, it means that you can search across a range of dates – something that is not possible in the Advanced Search page.

In version 4.04, Advanced search style functionality was added to the Record/File/Box search, although it shows a RecordPoint browser instead of SharePoint search results. Unfortunately, because of the implementation, the date fields cannot be searched in this Advanced search style view – they can only be added to the popular view. As such, you cannot use AND and OR specifiers on dates.

Importing

When importing DateTimes, the raw data from the import is NOT localised and is put into the back-end directly. As such, the imported data may have to be groomed in order to make the DateTime columns into a UTC format compatible with RecordPoint.

This is further complicated by the Field Type in the import mapping. Many users doing an import are inclined to use “System.DateTime” for the field type. However, this can be complicated by the imported data which may or may not be regionalised.

File types like Excel have formatting options for DateTimes which can further confuse the issue. For instance, 10/3/2017 in Excel is normally a date-time object, regionalised by the client machine’s local settings. The importer may think that the value put into RecordPoint should be 10/3/17, but they would be mistaken.

The recommended approach to importing DateTime columns is as follows:

In the import templates in RecordPoint, use System.String for the import mapping field type. Note that if using a custom column, the column type should still be DateTime. The field type is only used for import mapping.
In the import data, convert the localised dates to a UTC format in string type. An example formula for an Excel column might be as follows:
=IF(ISBLANK(F2),"",CONCATENATE(TEXT(F4-TIME(10,0,0),"yyyy-mm-ddTh:mm:ss"), ".0000000Z"))

Note that the above would only work for one specific time-zone (In this case, +10:00). The formula might have to be adjusted for different parts of the year for calculating in areas with Daylight Savings.