Convert timestamp to string without formatter

Question

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();

_"I get a TIMESTAMP from an Oracle DB in this format:"_ Then it appears you do not actually get a TIMESTAMP, or you do not understand what it is you actually _get_. I say this because the oracle data types TIMESTAMP and DATE don_not_ have _any_ human-readable format. They are oracle's internal, binary format. They only become 'yyyy-mm-dd' or 'dd-mon-yyyy' or 'ddmmyyyy' or any other human-readable format when they are returned from a TO_CHAR function. — EdStevens, Oct 29 '21 at 19:21

score 2 · Answer 1 · answered Oct 29 '21 at 18:31

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.

Arvind Kumar Avinash · Answer 2 · 2021-10-29T21:08:11.117

java.time

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

ANSI SQL	Java SE 8
DATE	LocalDate
TIME	LocalTime
TIMESTAMP	LocalDateTime
TIME WITH TIMEZONE	OffsetTime
TIMESTAMP WITH TIMEZONE	OffsetDateTime

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.}

score 1 · Answer 3 · answered Oct 29 '21 at 18:29

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>

score 0 · Answer 4 · answered Oct 29 '21 at 20:33

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.

Convert timestamp to string without formatter

4 Answers4

java.time

How to format a LocalDateTime?

How to format a `LocalDateTime`?