Timestamp conversion with Loader API

[Şevket Gökay]

Hello everyone,

a very happy user of Jooq for more than 10 years here in my project: https://github.com/steve-community/steve

Recently, I wanted to introduce a feature to export and import all data as CSV files in one  ZIP file: https://github.com/steve-community/steve/pull/1874

Since the data can be big, exports are happening in batches with a Cursor in a streaming mode. Imports are using Loader API.

Now, this project is still using joda DateTime, so I have the necessary converter (https://github.com/steve-community/steve/blob/master/src/main/java/de/rwth/idsg/steve/utils/DateTimeConverter.java) and it is attached with maven plugin during codegen to each field. 

The problem I face is the following: Database timestamps are mapped to org.joda.time.DateTime and DateTime.toString() method is used during CSV serialization. I cannot modify this behavior (right?). Therefore, I have values such as "2025-11-20T17:13:20.389Z" in CSV. During the importing from CSV, the Loader API is trying to convert this directly to Timestamp, which fails and because of which I have null values in my tables.

How can I elegantly influence the CSV reading and writing with regards to the timestamp format? I saw the existence of org.jooq.ConverterProvider and have set it globally in my Jooq Config instance, but I saw that it was not used during imports. Do you have any tips? Am I missing something?

Kind regards,
Sevket

[Lukas Eder]

Hi  Sevket,

Regarding the CSV serialisation, there's a feature request for adding formatters:

https://github.com/jOOQ/jOOQ/issues/5673

This should address both issues that you're mentioning. You can obviously achieve the same thing already today by attaching a Converter to your DateTime field that converts the temporal value to a String format of your liking, directly in the query.

When importing, the listener API can probably help?

https://www.jooq.org/doc/latest/manual/sql-execution/importing/importing-listeners/

Best Regards,

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/jooq-user/c0b31fdd-fa3f-42cf-8d5f-8762c6aea416n%40googlegroups.com.

[Şevket Gökay]

Hey Lukas,

thank you for your response, and also for your work in general on Jooq! Funny that this issue is 9 years old and sorry for not being able to find it.

Actually, during serialization, I am fine with DateTime.toString() method being used since it produces the standard ISO8601 format. Not being able to read it back and import was my problem. I am not sure how to do it with the listener API.

Anyways, since I was not missing something and it is a literal feature request to be implemented, I went with the following approach:

1. Create a private/internal Converter for Timestamp <-> String, where String is ISO8601:
    private static class IsoTimestampConverter extends AbstractConverter<String, Timestamp> {

        private IsoTimestampConverter() {
            super(String.class, Timestamp.class);
        }

        @Override
        public Timestamp from(String str) {
            if (StringUtils.isEmpty(str)) {
                return null;
            }
            return Timestamp.from(Instant.parse(str));
        }

        @Override
        public String to(Timestamp ts) {
            return ts == null ? null : ts.toString();
        }
    }

2. using .fields(Field<?>... fields) with Loader API where I go over the fields of the table and use my custom IsoTimestampConverter, if that particular field is a timestamp field:
     private List<Field<?>> getTableFields(Table<?> table) {
        return Arrays.stream(table.fields())
            .map(it -> {
                if (it.getDataType().isTimestamp()) {
                    return DSL.field(it.getName(), SQLDataType.VARCHAR(50)).convert(isoTimestampConverter);
                } else {
                    return it;
                }
            }).toList();
    }
such that the actual usage becomes .fields(getTableFields(table)).

I was looking for a general solution, where I do not hardcode decisions for each table and its fields, since my tables and columns can change over time.

The full solution is at https://github.com/steve-community/steve/pull/1874/commits/747d863fee58616d7976aff9de64c7d8a03d95e4 if anyone is interested.

Regards,
Sevket

[Lukas Eder]

On Sun, Nov 30, 2025 at 4:56 PM Şevket Gökay <sevke...@gmail.com> wrote:

Hey Lukas,

thank you for your response, and also for your work in general on Jooq! Funny that this issue is 9 years old and sorry for not being able to find it.

Well, jOOQ is 15 years old, so 9 years old issues are not surprising.

  

Anyways, since I was not missing something and it is a literal feature request to be implemented, I went with the following approach:

Thanks for sharing this here. This will be useful for others.

I was looking for a general solution, where I do not hardcode decisions for each table and its fields, since my tables and columns can change over time.

You can implement a configuration where you map Field<T> -> Field<U> for this purpose. In that configuration, I would imagine you should be able to re-use converters, and other logic.