DataSourceField Definition for DateTime Stored as Epoch

[chimpeenuts]

1) v8.3p_2012-11-25/PowerEdition Deployment (built 2012-11-25)

So I can't imagine I'm the first person trying to do this, but nothing pops up in the forum.

I have a SQL database table with a datetime value stored as milliseconds since Jan 1, 1970 00:00:00. The format of the database column is double.

Here's some example values:

1354037674595
1354038674755
1354024674791
1354035674836
1354054674879
1354034674912
1354034674954
1354016675003
1354034675048
1354034675085

I'm using .ds.xml files and would like to map this column to a Smart GWT field type of 'datetime'. I'd then like to display this value in a ListGrid and show it as a human readable time...soemthing in the format of MM/dd/yyyy HH:mm:ss.

The grid shows the filter editor and I'd like to be able to allow the user to filter the grid data using the DateTime picker.

I'd like to believe that Smart GWT has features and capabilities to support all my needs above but I haven't been able to get it to work.

Here's my datasource definition for that field:

    <field name="time" type="datetime" sqlStorageStrategy="number" title="Time" canEdit="false" />

I get errors when the page loads:

=== 2012-12-03 19:59:51,817 [l0-7] WARN  SQLTransform - [builtinApplication.device_fetch] Unable to parse a valid date, time or datetime out of value 1.354034675085E12 using format string yyyyMMdd

I tried using sqlDateFormat but that assumes the date is being stored in as a number like 20121202 when in fact my storage strategy is the actual epoch value not a number matching yyyyMMdd.

Here are my questions:
1) Does Smart GWT support epoch-stored datetime? If so, what should my field definition be?
2) If not, what's the best way to handle my situation (sql convert to date in operatingbinding? custom formatter and handling filter operation? etc)
3) Do I need to implement a custom cell formatter for that datagrid field?
4) Will the default Grid FilterEditor for this column work as expected?
5) Is there any way to allow the DateTime Picker for the filter editor to include seconds (right now it just appears to allow hours and minutes).

Thanks

[Isomorphic]

sqlStorageStrategy:"number" is close, but unfortunately there is no Java SimpleDateFormat that would parse milliseconds since epoch.

If you are handy with SQL, dataSourceField.customSelectExpression, customUpdateExpression and customCriteriaExpression can be used to create a kind of "virtual field" where you map between the Java date object and SQL representation via Velocity templates. This would generally be the highest performance and from the server-side DataSource layer on up, the field would be a normal "datetime" field.

If you're less comfortable with SQL, two-way conversion in Java is the right approach. In this case, you may want to declare both a "datetime" field and a normal "int" field, with the latter marked hidden:true. This would allow you to convert AdvancedCriteria submitted against the "datetime" field to equivalent AdvancedCriteria against the "int" field.

Regardless of approach, yes you'd need a custom formatter to reveal seconds.

[chimpeenuts]

Isomorphic -

Thanks for the reply.

I was able to add a customSelectExpression to my datasourcefield in my .ds file.

Since I'm using postgres, it turned out to be:

<field name="time" type="datetime" title="Time" canEdit="false" customSelectExpression="(TIMESTAMP 'epoch' + time * INTERVAL '1 second')" />

Since this field is read-only I don't need to worry about a update or delete sql custom statement.

In terms of getting my dates to display as desired in my listgrids, instead of using a cell formatter (since now the data is datetime), I just set my global display formatters to show the format I wanted. I was even able to add SECONDS to the DateTimePicker for listgrid filters!

		// Set the global date format
		DateDisplayFormatter formatter = new DateDisplayFormatter() {
			public String format(Date date) {
				if (date == null)
					return null;
				final DateTimeFormat dateFormatter = DateTimeFormat
						.getFormat("MM/dd/yy HH:mm:ss");
				String format = dateFormatter.format(date);
				return format;
			}
		};
		DateUtil.setNormalDateDisplayFormatter(formatter);
		DateUtil.setShortDatetimeDisplayFormatter(formatter);
		DateUtil.setShortTimeDisplayFormatter(formatter);
		DateUtil.setShortDateDisplayFormatter(formatter);

As always, thanks for the quick help!

[Isomorphic]

Just a note that we went ahead and added special javaDateFormat strings "$epoch" and "$epochms" to allow storing epoch time as seconds and milliseconds respectively. This is 4.0 only.

[chimpeenuts]

Just a note that we went ahead and added special javaDateFormat strings "$epoch" and "$epochms" to allow storing epoch time as seconds and milliseconds respectively. This is 4.0 only.

This is totally awesome! Otherwise my datasource .ds files were having to include database-specific SQL (or at least for Postgres which has always been a little wonky).

Will see if I have access to the 4.0 nightlies...

Thanks! Appreciate the new feature. I was a little surprised that you could provide a textual format for the date, e.g. MMddYYYY for dates stored in the database as 10112012 (so October 11, 2012) which seems like a terrible idea to me but there was no easy way to turn a common date/time universal format (good old UTC) into a standard datetime object!

[Isomorphic]

Yes, nightlies of 4.0 are available at smartclient.com/builds.

For whatever reason, people storing dates as text is what we encountered first; seems to be something people did in the early days of DB2.