Search code examples
oraclehibernatelocaledatetime-format

timestamp locale in hibernate using oracle

I have an entity class which has a timestamp property.

If I save an entity instance in oracle, the corresponding timestamp column will be written into Chinese format(I am using oracle 10g with Chinese locale).

22-8月 -11 07.04.03.926000 下午 is saved

But I want 2011-08-22 19:04:03.926

How can change column definition or database locale setting?

Solution

  • A timestamp in Oracle is a point in time. As such, there is no format stored with the data. When you retrieve data from a timestamp column, it is displayed by default in the format specified by your NLS_TIMESTAMP_FORMAT or NLS_TIMESTAMP_TZ_FORMAT session variable.

    You can always use a specific format with to_char:

    SQL> SELECT to_char(systimestamp, 'yyyy-mm-dd hh24:mi:ss.ff3') my_ts FROM dual;

MY_TS
-----------------------------
2011-08-22 14:38:48.351

    You could also set a new default for your session with:

    SQL> ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='yyyy-mm-dd hh24:mi:ss.ff3';

Session altered

SQL> select systimestamp from dual;

SYSTIMESTAMP
-------------------------------------------------
2011-08-22 14:42:23.776