Using DATETIME Casts

You can use the double colon operator (::) to cast GeoTimeRange values to a specific precision. This allows you to specify a less precise input specification than the GL_DATETIME setting. It does not, however, override the GL_DATETIME setting, nor does it extend text input.

In the following example, GL_DATETIME is set to YEAR TO DAY, and the DATETIME cast allows you to specify a GeoTimeRange value with a precision less than the minimum default of YEAR TO SECOND:

-- GL_DATETIME="%Y-%m-%d"
INSERT INTO trtab VALUES (GeoTimeRange('1997-01-02'::datetime year to day));
SELECT * FROM trtab;
tr  (1997-01-02,1997-01-02) 

In the following example, the cast allows you to specify less than the GL_DATETIME precision:

-- GL_DATETIME="%Y-%m-%d %H:%M:%S"
INSERT INTO trtab VALUES (GeoTimeRange('1997-01-02'::datetime year to day));
SELECT * FROM trtab;
tr  (1997-01-02 00:00:00,1997-01-02 00:00:00) 

The data, however, is zero-extended on input and output to YEAR TO SECOND.

Tip:

Instead of casting, you could insert data with hours, minutes, and seconds set to 0 (or any value). The hours, minutes, and seconds would be truncated to conform to the GL_DATETIME setting of YEAR TO DAY.

In the following example, the INSERT statement returns an error message because the cast is to YEAR TO DAY, while YEAR TO SECOND data is inserted:

-- GL_DATETIME="%Y-%m-%d %H:%M:%S"
INSERT INTO trtab VALUES (GeoTimeRange('1997-01-02 03:04:05'::datetime year to day));
SELECT * FROM trtab;
 1264: Extra characters at the end of a datetime or interval.

http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.geod.doc/geod55.htm