Overview

SSP deals with two logical types of date/time data:

1. Timestamp - A calendar day plus a time component, i.e. a particular moment or instant. E.g. Apr 23, 2013 8:58:28AM.
2. Day - Just a calendar day without a time component. Could represent a non-specific moment on that day, or the entire day. E.g. Apr 23, 2013.

This document describes how these two logical types are implemented in the database, server-side application, web API, and client-side application. It was originally prompted by work that resulted in this email thread.

TL;DR: SSP represents all date/time values as java.util.Date, instances of which it persists without timezone. Instances which represent date and time are persisted using a globally configured timezone for Hibernate-managed de/serialization. This timezone is configured once at application deployment and is not necessarily (and often isn't) the JVM default timezone. As long as the configured zone does not observe daylight savings, persistent Timestamp values will be unambiguous. Date-only values are interpreted in the JVM-default timezone. This matches the semantics of all such fields in the current feature set. SSP's web API distinguishes between these Timestamp and Day types on the wire by representing Timestamps as int values counting milliseconds since the Epoch and Days as formatted date strings. For the latter, the front and back-end do not necessarily need to deal with such values in the same timezone, but must always deal internally with such values in a single timezone. Even if/when represented by the same type/model, instances of Timestamp and Day are not interchangeable and cannot usually participate in heterogenous comparisons/calculations without coercion rules which may be field specific.

Timestamps

Timestamp handling is straightforward, but has historically been vulnerable to daylight savings-related ambiguities. To address that problem, the application supports global configuration of a persistent Timestamp timezone, which needn't be the JVM default timezone. This mechanism is discussed below. Because they are specific moments in time, Timestamps can usually be converted to local time before being presented to the end user without confusion, provided the user is given some indication of the zone in which these values are rendered.

Timestamps - Database

Timestamps are stored in Postgres as timestamp without time zone and in SQLServer as datetime. SQLServer 2005 does not have native timezone-aware date/time types. So all timezone awareness is at the application layer. Joda-Time, which has Hibernate bindings, would be a portable option for storing timezones in the database. But we have elected to not pursue that refactor since the patch would be quite large, and we are insufficiently confident it will not introduce other problems.

The Postgres datatype is now enforced by the PostgresDateTypeConverter Liquibase plugin. Dev list notification.

Timestamps - Server-Side Application

Timestamps are represented in the server-side application as java.util.Date, which is implemented internally as milliseconds since the Epoch. I.e. Timestamps do not themselves carry timezone information. But a global timezone is configured into the application to control interpretation and rendering of Timestamp values at the JDBC layer, where they are represented as formatted strings. To avoid storage of ambiguous values, this timezone should not observe daylight savings. The default is "UTC". Prior to SSP-1002, this timezone was the JVM default, which was installation specific and often not given careful thought.

Enforcement of the storage timezone in Hibernate is implemented by SspTimestampTypeDescriptor, which is based on a StackOverflow answer. Registering this as a type mapping override at startup (using a custom LocalSessionFactoryBean extension) allows us to avoid modifying existing @Temporal annotations. E.g. for JDBC deserialization:

public <X> ValueExtractor<X> getExtractor(final JavaTypeDescriptor<X> javaTypeDescriptor) {
	return new BasicExtractor<X>( javaTypeDescriptor, this ) {
		@Override
		protected X doExtract(ResultSet rs, String name, WrapperOptions options) throws SQLException {
			return javaTypeDescriptor.wrap( rs.getTimestamp( name, Calendar.getInstance(SspTimeZones.INSTANCE.getDbTimeZone()) ), options );
		}
	};
}

SspTimeZones is configured by Spring and the timezone value being retrieved in the example above can be set in ssp-config.properties via the db_time_zone property. The reference to a static INSTANCE is a side-effect of this configuration needing to be available in Jackson- as well as Spring-instantiated de/serialization classes.

Timestamps - Web API and Client-Side Application

Timestamps in the web API use Jackson default de/serialization behavior. When rendering a response, this results in integer fields representing milliseconds since the Epoch (i.e. Unix/Posix time but with greater precision). I.e. we simply copy the underlying value of the java.util.Date into the JSON. When reading a response, Jackson accepts the same integer type and a number of string formats. The latter are interpreted in UTC. This works well with our Ext.js code, which readily consumes our int values, renders the resulting dates in correct, browser-local time, and writes formatted strings in UTC time in its requests back to the server. Because we're dealing with discrete moments in time, though, the zone in which they are interpreted doesn't matter and, unlike with Days, the JSON zone doesn't need to match the database zone. In reality, the main problems we face are mainly design issues (or at least the first one is):

1. The end user has no idea what timezone on-screen Timestamps are rendered in. If you assume most users are "in" the same timezone as their school, this is usually a non-issue
2. In several cases we store Timestamps on the server but the user is only offered a date selection widget, e.g. Journal Entries.
3. Rendering dates in arbitrary, user-selected timezones will be difficult. We would either need to pre-render such values server-side or apply timezone offset arithmetic to the UTC milliseconds prior to rendering. See additional discussion of this issue in the sections on Days handling below.

JSON date deserialization is unremarkable. JSON Serialization is more interesting. The output is defined by Ext.JSON.encodeDate(), the format of which is "yyyy-mm-ddThh:mm:ss". The implementation is such that this format is applied in local time. But because the server expects UTC time, we have a fix up function FormRendererUtils.fixDateOffsetWithTime() which creates a new Date built from the original date's UTC representation. I.e. it "tricks" Javascript into thinking local time is UTC time:

fixDateOffsetWithTime: function( dateToFix ) {
	return new Date(dateToFix.getUTCFullYear(), dateToFix.getUTCMonth(), dateToFix.getUTCDate(),  dateToFix.getUTCHours(), dateToFix.getUTCMinutes(), dateToFix.getUTCSeconds());
}

Ext.js Writer classes have configuration hooks for rendering date/time values. SSP's AJAX calls are all implemented with direct Ext.Ajax calls rather than the Ext.js Store/Proxy abstractions. There might be some way to use Writer's with the former, but that has not been the convention to date

Days

Day handling is fraught. The application represents these values as midnight on a particular calendar day, so database de/serialization must occur in the context of a particular timezone. But when representing such values to end users, the timezone is usually irrelevant. For example, when Apr 23, 2013 at midnight EDT is represented naively to an end user in MDT, it will be converted to Apr 22, 2013. This is usually not what a user expects. For a birth date it is definitely not what the user expects. It would also probably be unexpected for an admittance or journal entry date since a user doesn't actually think of those things as occurring at midnight, or at any particular time of day. In fact, we currently have no use cases where a Day value should be converted to local time before being rendered. And doing so naively in read/write fields has historically led to bugs.

We also have use cases where Timestamps are rendered on-screen as Days. For example, the Main tool's Recent Activity panel only renders the date portion of the underlying Timestamp. Same for Early Alert Responses. But it would be incorrect in these cases to model the underlying data as a Day.

Days - Database

Days are stored in Postgres and SQLServer 2008 and later as date. This type has no time nor timezone component in either platform, which is what we want. In SqlServer 2005 and earlier, Days are stored as datetime. We had to do this because pre-2008 SQLServer versions had  no equivalent of date. So we stick with datetime and rely on Hibernate to zero out the time component on reads and writes. Unfortunately, Liquibase's default type mappings map the logical Liquibase date type to smalldatetype for SQLServer, which is incompatible with Hibernate's handling of java.sql.Date. So we introduced two extensions/plugins to override the default Liquibase type mappings: MSSQL2008DateTypeConverter and MSSQL2005DateTypeConverter. These extensions are picked up automatically at runtime by virtue of being defined in the liquibase.ext package.

Days - Server-Side Application

The server-side application represents Days and Timestamps with the same Java type: java.util.Date. So are both are modeled as moments in time. I.e. Days are implemented as Timestamps set to midnight in the JVM-default timezone.

Without additional knowledge of the semantics of the specific Day instance, you cannot, for example, use java.util.Date.before() to compare a Day to a Timestamp because the Day could represent an entire calendar day rather than a specific moment in time.

A custom Hibernate SspDateTypeDescriptor serves the same purpose for Days as SspTimestampTypeDescriptor does for Timestamps. The former will be used automatically for any persistent field tagged with @Temporal(TemporalType.DATE). SspDateTypeDescriptor was originally introduced when we expected to support either forcing both Days and Timestamps to use the same persistent timezone or to allow both to be independently configurable. They are both technically still independently configurable, but the timezone for Days is always the JVM-local timezone. Using the same timezone for both proved to unnecessarily complicate what should be simple operations, such as looking up all records having the current Day as determined by JVM-local time.

We do not currently make any attempt at any other layer of our application to model persistent Days as anything other than java.util.Date nor enforce rules that such fields always be set to a "midnight-ish" value. For now the defensive copying that goes on in Date accessors seems to be sufficient to insulate us from any unexpected side-effects of using java.sql.Date in other layers of the application (as is specifically warned against in Jackson docs.) And the complexity cost of enforcing "midnight-ish" times outside of the Hibernate layer has yet to show how it would pay for itself. Currently it would just be so much duplication.

You cannot use stock Hibernate validations, e.g. @Past on Days fields.

Days - Web API and Client-Side Application 

Prior to SSP-1040, the web API represented Days as Timestamps by writing them into JSON as a numeric value representing milliseconds elapsed since the Epoch (i.e. Unix/Posix time but with greater precision). I.e. we simply copied the underlying value of the java.util.Date into the JSON. The in-browser application parsed these values into Javascript Dates which would then be rendered on screen in browser-local time. This resulted in unexpected on-screen dates as described above.

But it is hard to render Javascript Dates client-side in arbitrary timezones - you can either render in UTC time or browser-local time. But the given millisecond value represents midnight in an arbitrary timezone, not necessarily UTC. So for UTC time rendering to work, we would need to build something to reverse-offset the millisecond value by that timezone's UTC offset. Not impossible, but mind-bending, and if not done carefully, still runs the risk of accidentally writing unintended changes back to the server.

Instead, we elected to stick with browser-local rendering and change the API representation of Days to "pre-render" the date. Going forward, any Day field mapped into the web API will require special annotations, as demonstrated by PersonTO:

@JsonSerialize(using = DateOnlySerializer.class)
@JsonDeserialize(using = DateOnlyDeserializer.class)
private Date birthDate;

So now birth dates, for example, are sent as '2013-04-23' rather than a long integer. This is valid to the extent that the time portion of Days is truly irrelevant. And it makes it easy for the in-browser app to store the value as a simple string when that makes the most sense. Note that even though Ext.js has support for interpreting dates with timezones included in their format, passing the server-side Day-handling timezone does not help with our use case. If storing these fields as strings, we would likely need to strip out the timezone. Or we would need to parse to a date, reverse-apply the timezone's UTC offset, then apply the browser-local UTC offset so we eventually end up with a timestamp in browser-local midnight.

So now JSON deserialization looks like this, from the Person.js model definition where we specify a standard logical ISO format rather than the 'time' format you'll see on Timestamp fields:

{name: 'birthDate', type: 'date', dateFormat: 'c'}

JSON serialization quirks discussed for Timestamp types also apply to Days. But we needed more control over serializing the latter. This is now handled by FormRendererUtils#toJSONStringifiableDate(). Code which handles form serialization is responsible for calling this method for any Day field being sent to the server. This method is effectively a replacement for FormRendererUtils#fixDateOffset(), though, so this is not a new burden. Please see comments in the source code for a more detailed rationale for this function's design. Essentially, it is by-passing Ext.JSON.encodeDate() by returning a non- Date object which happens to implement a toJSON() function. Rendering the given Date in local time works just fine because both the original Date string and any user-specified values, including those sourced from date pickers, would have also been interpreted in local time. The resulting formatted string might be interpreted in a different timezone server side, but that doesn't matter - the same date component will still be written through to the database. I.e. the two parties participating in an API needn't agree on the timezone in which a Day value should be interpreted, but internally each must consistently apply a single timezone wherever Day values are used.

toJSONStringifiableDate: function ( origDate ) {
        return {
            formattedStr: Ext.Date.format(origDate, 'Y-m-d'),
            toJSON: function() {
                return '"' + this.formattedStr + '"';
            }
        }
    }

External Data

The following external data fields were redefined as part of work on unifying date/time handling and will impact anyone upgrading beyond 1.2.0:

external_person.birth_date - Previously timestamp with time zone in Postgres and datetime in SQLServer. Narrowed to date for SQLServer 2008. Kept as datetime in earlier SQLServer versions b/c date was not introduced until 2008. This cannot be turned off except by disabling external data management altogether. It should be safe, though, See comments in 000031.xml changeset for an explanation of why.

external_student_test.test_date - Same handling as external_person.birth_date

external_term.start_date, external_term.end_date - Data type stays the same, existing values converted to currently configured db_time_zone in ssp-config.properties. Interprets current values in the time zone specified by db_time_zone_legacy in ssp-config.properties. By default the app will set the latter to the current JVM timezone. To cause this data to not be converted set db_liquibase_convert_external_term_timestamps=false. The default is true, which makes sense for both upgrades and fresh installs. Would only be set to false for an upgrade scenario where these fields have already been converted by an external process. Since the relevant changeset will never run if external data management is disabled, it should always be safe to run the conversion on a fresh install since you know the affected table will be empty, SSP having just created it. For more see SSP-1117

Migrations

Prior to SSP-1040 (SSP 1.2.0 and earlier), all date/time values were represented in the database as Timestamps. The first to be migrated to a Day type was the Person.birthDate, ExternalPerson.birthDate pair. For this we simply narrowed the database fields to date (except in SQLServer 2005, as described above). Both database fields were actually timestamp with time zone in Postgres. Which meant that the underlying value was stored in UTC, so a narrowing operation would give you the UTC date component, not the date component in the timezone in which the value was originally stored. This actually turns out to be OK, though. Suppose you stored Oct 10, 1990 00:00:00-04. Internally, Postgres would store Oct 10, 1990 04:00:00-00. So chopping off the time component leaves you with the same date component.  This would not work for deployments with positive UTC offsets. We are unaware of any such deployments at this writing (Apr 2013).

Our Liquibase scripts will automatically attempt to convert all existing "internal" timestamp without time zone (Postgres) and datetime (SQLServer) values to the currently configured db_time_zone in ssp-config.properties. It does this by interpreting current values in db_time_zone_legacy which defaults to the current JVM timezone. This is usually what you want for both upgrades and fresh installs. But it can be turned off by setting those two configs to the same value. Note that db_time_zone does not support the special "CURRENT_JVM_DEFAULT" value.

For MSSQL these conversions are handled with a Liquibase "custom change" plugin: MSSQLDateTimeTimezoneRefactor. E.g. from 000032.xml:

<customChange class='org.jasig.ssp.util.liquibase.MSSQLDateTimeTimezoneRefactor'>
	<param name='tableName' value='appointment' />
	<param name='columnName' value='created_date' />
	<param name='origTimeZoneId' value='${database.timezone.legacy}' />
	<param name='newTimeZoneId' value='${database.timezone}' />
</customChange>

For Postgres we can accomplish the same thing with inlined SQL. From 000033.xml:

<update tableName='appointment'>
	<column name='created_date' valueComputed='(created_date AT TIME ZONE &apos;${database.timezone.legacy}&apos;) AT TIME ZONE &apos;${database.timezone}&apos;' />
</update>