Developer Resource - Schema Metadata

Within the Dovetail/Clarify schema, there's a number of attributes at the schema level for defining how a particular field should behave. For example, if a field has a GenericFieldId of 3, that means it's a unique field. There are also attributes for CHANGE_DATE, CHANGE_FIELD, CURRENCY, etc. Dovetail can support additional attributes by the use of a schema.metadata.config file.

Note: The schema.metadata.config file is read at application startup and cached in memory. If that file is changed, the application must be restarted to pick up the changes.

Support for Date-only fields

Within the Dovetail/Clarify schema, all date/timestamps are stored as a full datetime in the database, including a date and a time. All date/timestamps are converted to the default time zone, and stored in the database in that timezone. For example, if your database server (and default time zone) are set to CST, then all date/timestamps are stored in the database in CST.
There is no distinction between a date-only field and a date-time field in the database. They're all stored with the same datatype. A date-only field would be stored in the database as the date + midnight. e.g. 2024-08-15 00:00:00.000. A date-time field would be stored in the database as the date + the specific time. e.g. 2024-08-15 08:05:00.000.

When a datetime is normally displayed in the client application (such as Dovetail Agent), that date/timestamp is converted to the user's timezone. For example, if the client is in PST, a date/timestamp of 1980-03-18 12:00:00 AM CST would be converted to 1980-03-17 10:00:00 PM (as PST is 2 hours behind CST).

Any date/timestamps from the database are sent to the client browser in UTC. These timestamps are then converted to the browser's timezone on the client using Javascript. That way all timestamps are presented in the local timezone of the user. When a user enters a date/timestamp, that timestamp is converted to UTC before it is sent to the server. The server then converts it from UTC to the database's timezone. The database timezone is determined by the entry in table_time_zone where is_default = 1. All timestamps are stored in the database in that database timezone.

This allows users in different timezones to view date-time fields in their timezone, without having to do time conversions in their head (which can get especially tricky when you add in daylight savings times).

However, there are times when you wish to have a field that is only a date (without a time), and no timezone conversion is desired. For example, suppose you are storing a contact's date of birth. Lets say that their date of birth is March 18 1980. Regardless of how that data is entered (or from what time zone the client is in) - it should be March 18 1980. Because it has to be stored as a full datetime (meaning a date and time), it would actually be stored in the database as March 18 1980 at midnight (1980-03-18 12:00:00 AM).

Regardless of where the client is located, you want that to display as March 18, 1980 (with the time portion of midnight hidden). You don't want the timezone conversion to happen, and have it displayed as March 17, 1980 (with the time portion of 10:00:00 PM hidden).

Dovetail supports this scenario by defining a custom schema attribute of dataType="date" for that field within the schema.metadata.config file.

For example:

<?xml version="1.0" encoding="utf-8" ?>
<schemaMetadata>
  <table name="contact">
    <field name="x_dob" dataType="date" />
  </table>
</schemaMetadata>

This is supported when: