CODEWITHSUNDEEP
javasqlkotlintimejooq
Ilya Y
Ilya Y

Reputation: 854

how to compare only by date (without time) in jooq?

I have column with type timestamp with time zone. The table contains the following value "2022-06-30 22:56:29.183764 +00:00". I want to do a date-only search.

how to compare only by date (without time) in jooq?

I've tried this, but it doesn't work.

.where(MY_TABLE.UPDATED_AT.cast(LocalDate::class.java).eq(cashDate))

where cashDate = "2022-06-30"

Upvotes: 7

Views: 990

Answers (1)

Panagiotis Bougioukos
Panagiotis Bougioukos

Reputation: 19193

You have to provide your own converter for the conversion you want to have between Timestamp and LocalDate.

Check a similar example from jooq.

In your case it would be

import java.sql.Timestamp;
import java.time.LocalDate;

import org.jooq.Converter;

    public class LocalDateConverter implements Converter<Timestamp, LocalDate> {
    
        @Override
        public LocalDate from(Timestamp t) {
            return t == null ? null : t.toInstant().atZone(ZoneId.systemDefault()).toLocalDate();
        }
    
        @Override
        public Timestamp to(LocalDate u) {
            return u == null ? null : Timestamp.valueOf(u.atTime(LocalTime.MIDNIGHT));
        }
    
        @Override
        public Class<Timestamp> fromType() {
            return Timestamp.class;
        }
    
        @Override
        public Class<LocalDate> toType() {
            return LocalDate.class;
        }
    }

In the above example I have used ZoneId.systemDefault() for the conversion of timestamp to localdate and also LocalTime.MIDNIGHT for conversion of localdate to timestamp. This might be a bit dangerous and should be investigated from you if another specific zoneId should be used for your systems to work as expected.

Then you can use it as

.where(MY_TABLE.UPDATED_AT.asConvertedDataType(new LocalDateConverter()).eq(cashDate))

Upvotes: 2

Related Questions