How to convert bytea to joda.time.DateTime or to Timestamp?

Question

In my database I have a column of type bytea. I want to retrive that value and convert to date time.

This is how my value look like:

\xaced0005737200166f72672e6a6f64612e74696d652e4461746554696d65b83c78646a5bddf90200007872001f6f72672e6a6f64612e74696d652e626173652e426173654461746554696d65fffff9e14f5d2ea30200024a0007694d696c6c69734c000b694368726f6e6f6c6f677974001a4c6f72672f6a6f64612f74696d652f4368726f6e6f6c6f67793b787000000168cd2d829e737200276f72672e6a6f64612e74696d652e6368726f6e6f2e49534f4368726f6e6f6c6f67792453747562a9c811667137502703000078707372001f6f72672e6a6f64612e74696d652e4461746554696d655a6f6e652453747562a62f019a7c321ae30300007870770500035554437878

My access code to dataBase:

ResultSet resultSet = statement.executeQuery("SELECT * FROM public.history");
while (resultSet.next()) {
           System.out.printf("\n acquire_date: " +  resultSet.getString("acquire_Date") );
    }

How to convert that value to joda DateTime or to java.util.Date? I want the format of time to be like: yyyy-MM-dd'T'HH:mm:ss'Z'

Database view

`bytea` is a byte array in PostgreSQL. A byte array can contain any sequence of bytes. Are you sure that those bytes contain a date? (it seems MUCH too long for that) — aditsu quit because SE is EVIL, Feb 09 '19 at 10:14
Postgresql bytea is mapperd to Java byte array. So get the values as byte[] data = rs.getBytes("acquire_Date") and then convert to long. Value looks quite long for long integer — vmaroli, Feb 09 '19 at 10:17

score 1 · Accepted Answer · answered Feb 09 '19 at 10:55

It looks like you actually have org.joda.time.DateTime objects serialized to byte arrays and stored as such in the database (which is a TERRIBLE way to store dates). You just need to deserialize those arrays back to DateTime objects. Here's some proof of concept code using your example value:

import java.io.ByteArrayInputStream;
import java.io.ObjectInput;
import java.io.ObjectInputStream;

import javax.xml.bind.DatatypeConverter;

import org.joda.time.DateTime;

public class SerialTest {
    public static void main(final String... args) throws Exception {
        final String s = "aced0005737200166f72672e6a6f64612e74696d652e4461746554696d65b83c78646a5bddf90200007872001f6f72672e6a6f64612e74696d652e626173652e426173654461746554696d65fffff9e14f5d2ea30200024a0007694d696c6c69734c000b694368726f6e6f6c6f677974001a4c6f72672f6a6f64612f74696d652f4368726f6e6f6c6f67793b787000000168cd2d829e737200276f72672e6a6f64612e74696d652e6368726f6e6f2e49534f4368726f6e6f6c6f67792453747562a9c811667137502703000078707372001f6f72672e6a6f64612e74696d652e4461746554696d655a6f6e652453747562a62f019a7c321ae30300007870770500035554437878";
        final byte[] bytes = DatatypeConverter.parseHexBinary(s);
        // you would probably use bytes = resultSet.getBytes("acquire_Date") to get the byte array

        final ByteArrayInputStream bis = new ByteArrayInputStream(bytes);
        final ObjectInput in = new ObjectInputStream(bis);
        final DateTime dt = (DateTime) in.readObject();

        System.out.println(dt);
    }
}

Result: 2019-02-08T12:55:38.398Z

Also be aware that there are some potential security issues when deserializing values in java.

Thank you, it worked . I know is terrible to store like that in database. This is the reason why i want to convert all the column to timestamp with java.util.Date in app. Do you think java.util.Date with timestamp in database, it's the good format to store? — Buda Sergiu Flavius, Feb 09 '19 at 11:25
@SergiuFlavius yes, in pg you should probably use timestamp, with or without time zone. If you want to store moments in time, use timestamp with time zone, which corresponds to Date or Instant (also joda DateTime, I think). If you want a certain day and clock time (which can represent different moments depending on the time zone), use timestamp without time zone, which corresponds to LocalDateTime. — aditsu quit because SE is EVIL, Feb 09 '19 at 11:38

How to convert bytea to joda.time.DateTime or to Timestamp?

1 Answers1