0

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?

xiaohan2012
  • 9,870
  • 23
  • 67
  • 101

1 Answers1

0

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
Vincent Malgrat
  • 66,725
  • 9
  • 119
  • 171
  • Thanks. But NLS_TIMESTAMP_FORMAT applies only to the current session. How can I impose it to every session in hibernate? – xiaohan2012 Aug 22 '11 at 12:45
  • This is a client settings, I don't know if it is possible to set those variables in a config file of your client. You can however execute the statement at the beginning of each session or even [use a logon trigger](http://stackoverflow.com/questions/447608/how-to-change-default-nls-date-format-for-oracle-jdbc-client/447965#447965). – Vincent Malgrat Aug 22 '11 at 13:03
  • Also note that you can use [`Locale.setDefault`](http://stackoverflow.com/questions/1079985/nls-lang-setting-for-jdbc-thin-driver/1306042#1306042) to preset several NLS variables. Try `Locale.setDefault(Locale.ENGLISH)` before opening a connection. You still may have to set a specific session variable if they are not "default" for the specified territory. – Vincent Malgrat Aug 22 '11 at 14:08
  • 1
    @xiaohan2012 : when retrieving e timestamp from the database with Hibernate, you'll get a java.util.Date (or subclass) instance. This is just a point in time as well, without formatting. Use SimpleDateFormat to display this date as you want. – JB Nizet Aug 22 '11 at 16:21