I get a TIMESTAMP from an Oracle DB in this format: yyyy-MM-dd hh:mm:ss.SSS and would like to convert to a String like dd.MM.yyyy. I performed string operations and the result was correct as expected. Is this approach right or wrong? Or is it more efficient to use as a formatter? Do I have to use formatter if Oracle returns a different string in the future because of a different NLS_LANG setting?

String date = data.toString().substring(0,10).replace("-","."); // data is received from DB 
String day = datum.substring(8,10); 
String month = datum.substring(4,8); 
String year = datum.substring(0,4); 
String myDate = new StringBuilder().append(day).append(month).append(year).toString();

CodePudding user response：

I don't know Java, but - from my Oracle-ish point of view, correct way to do that is to use to_char function:

SQL> create table test as
  2  select systimestamp ts from dual;

Table created.

SQL> desc test
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TS                                                 TIMESTAMP(6) WITH TIME ZONE

SQL> select * from test;

TS
---------------------------------------------------------------------------
29.10.21 20:26:17,934000  02:00

This is how I'd do it, in Oracle:

SQL> select to_char(ts, 'dd.mm.yyyy') result
  2  from test;

RESULT
----------
29.10.2021

SQL>

If you can't use it from Java, would a custom function do? It accepts timestamp and returns string formatted as you wanted; you'd then call that function from Java:

SQL> create or replace function f_format_ts (par_ts in timestamp) return char
  2  is
  3  begin
  4    return to_char(par_ts, 'dd.mm.yyyy');
  5  end;
  6  /

Function created.

SQL> select f_format_ts(a.ts) from test a;

F_FORMAT_TS(A.TS)
--------------------------------------------------------------------------------
29.10.2021

SQL>

CodePudding user response：

I get a TIMESTAMP from an Oracle DB in this format: yyyy-MM-dd hh:mm:ss.SSS

With this statement you already went into the world of string processing. One of the reasons why you do not want to go there is timezones.

So actually via your JDBC driver you should have received a ResultSet. Probably you went with one of the getString() methods and think of the next step.

Alternatively you could have used getTimestamp() or even getDate(), which would make further conversion easier or even obsolete.

CodePudding user response：

java.time

The mapping of ANSI SQL types with java.time types have been depicted as follows in this Oracle's article:

Given below is a sample code to retrieve a LocalDateTime from columnfoo:

Statement st = conn.createStatement();
ResultSet rs = st.executeQuery("SELECT * FROM mytable WHERE <some condition>");
while (rs.next()) {
    // Assuming the column index of columnfoo is 1
    LocalDateTime ldt = rs.getObject(1, LocalDateTime.class));
    System.out.println(ldt);
}
rs.close();
st.close();

How to format a LocalDateTime?

You can use DateTimeFormatter to format a LocalDateTime.

Demo:

import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.Locale;

public class Main {
    public static void main(String[] args) {
        // This is a dummy LocalDateTime for the purpose of demo. You will retrieve
        // LocalDateTime from the DB as shown above
        LocalDateTime ldt = LocalDateTime.now();
        DateTimeFormatter dtf = DateTimeFormatter.ofPattern("dd.MM.uuuu", Locale.ENGLISH);
        String formatted = ldt.format(dtf);
        System.out.println(formatted);
    }
}

Output:

29.10.2021

ONLINE DEMO

Learn more about the modern date-time API^* from Trail: Date Time.

^{* If you are working for an Android project and your Android API level is still not compliant with Java-8, check Java 8 APIs available through desugaring. Note that Android 8.0 Oreo already provides support for java.time.}

CodePudding user response：

You appear to not be getting a TIMESTAMP value from the database; you are getting a string value from the database (that just happens to contain date/time data). Don't do that when you can just get the TIMESTAMP as a binary value that you can easily format.

If you want to get a timestamp then retrieve it as a LocalDateTime:

LocalDateTime datetime = resultSet.getObject(columnIndex, LocalDateTime.class);

The you can format it to whatever format you want using LocalDateTime.format(formatter).

Since you only want the date components you could even use LocalDate rather than LocalDateTime.