Handle Timestamp with Time Zone types where supported
Description
Environment
Oracle 10g
HSQLDB 1.9.x
Potentially others
Activity
Show:
Marcin Kłopotek June 8, 2016 at 9:41 AM
The fix for this introduced https://liquibase.jira.com/browse/CORE-2784#icft=CORE-2784 regression.
Nathan Voxland March 11, 2016 at 7:59 PM
Fixed for oracle, postgresql and hsql
Former user January 10, 2015 at 4:44 PM
Surprisingly, for PostgreSQL a non-standard "TIMESTAMPTZ" declaration is recognized and converted to "TIMESTAMP WITH TIME ZONE".
Former user January 10, 2015 at 4:41 PM
At least PostgreSQL does as well (for decades at least):
Fixed
Details
Details
Reporter
Adam Karl
Adam KarlComponents
Fix versions
Affects versions
Priority
MinorCreated November 20, 2009 at 1:21 PM
Updated June 8, 2016 at 9:43 AM
Resolved March 11, 2016 at 7:59 PM
In my current Oracle database structure I have a column defined with the type "TIMESTAMP(6) WITH TIME ZONE". This syntax is also supported by HSQLDB so I was attempting to create a generic mapping that would apply to both databases via Liquibase change logs using the syntax below.
<column name="UPDATE_TIME" type="TIMESTAMP(6) WITH TIME ZONE" />Against both Oracle and HSQLDB this results in a TIMESTAMP column which is not correct since a timestamp does not include time zone information. I utilize a workaround using the "modifySql" tag below.
<column name="UPDATE_TIME" type="TIMESTAMP_TZ" /> ... <modifySql><replace replace="TIMESTAMP_TZ" with="TIMESTAMP(6) WITH TIME ZONE" /></modifySql>At the very least Liquibase should not interpret TIMESTAMP(#) WITH TIME ZONE as a TIMESTAMP against databases that support TIME ZONE columns. My two database examples support the same syntax between them so minimally Liquibase could pass the type information without any translation when it finds syntax which it cannot/should not interpret. I'm tempted to call this simply a bug in the time column type detection so the fix may be very simple but could optionally include full translation logic for TIMESTAMP WITH TIME ZONE column types.