Develop Standard and Consistent Day/Time Formats

Rob Tuffly 1 year ago in Insights / Reporting updated by Tarun Chawla 4 months ago 1

System needs standardized day/time formats across all objects. Some base attributes are date only; others are date and time-stamped. This inconsistency requires rewriting existing system, SQL and BI reports that rely on these attributes. When developing new reports that include data from previous versions of Aprimo (V9, for example), risk of exporting inaccurate data could increase due to this inconsistency.


Generally speaking, Aprimo will store system-generated datetime fields in GMT and adjust these based on the timezone the user has set, such as created dates, modified dates, start dates, end dates, and audit trail entries. 

Extended attributes are always stored as-is without a GMT conversion, so if a user enters a datetime EA as 5pm and they are in Europe, another user who is in the US will still see that value as 5pm when they view the EA. 

This is a question that has come up in the past. Generally system-generated dates should convert to the user's timezones (as the server is on UTC and would not make sense to the user), whereas a user-entered date in an EA may need to reflect either way, but today Aprimo does not allow timezone-specific datetime EAs. 

When reporting out of Aprimo there are a variety of ways to surface these dates - directly in the out of the box objects as well as via sql views - and you can adapt conversions there as needed. We recently added a 'GMT Indicator' field as well on custom SQL objects that allow for greater control of this.

I'm going to shelve this request as the system does work the way it's intended to today, however, if you do need additional help with this we can connect you with some analytics experts at Aprimo that can help sort out and explain your data and how things get converted.