Spring boot convert epoch timestamp to java.time.LocalDate?

Question

I want to compare LocalDate with the timestamp in database (Postgres with UTC timezone).

code I wrote

@Query("""
      select eve from Events eve
      where cast(to_timestamp(pe.timestamp) as LocalDate)  = :curDate
   """)
  List<Event> getAllByDate(LocalDate curDate);

I added both

  @PostConstruct
  public void init(){
    // Setting Spring Boot SetTimeZone
    TimeZone.setDefault(TimeZone.getTimeZone("UTC"));
  }

and

spring.jpa.properties.hibernate.jdbc.time_zone=UTC

then I tried to run

select to_timestamp(eve.timestamp)

inside @Query where eve.timestamp was 1664584019 and it converted into 2022-10-01 18:39:59.0

First I through it is converting into IST (Indian standard time) but I think that also wrong.

So, Can anybody help me regarding how to convert epoch time to LocalDate to compare with LocalDate?

1664584019 equals GMT: Saturday 1. October 2022 00:26:59. So it seems you got the correct date in this case, but where the time 18:39:59.0 comes from is a mystery to me. — Ole V.V., Jan 06 '23 at 12:21
In Java it’s easy enough: `Instant.ofEpochSecond(1664584019).atOffset(ZoneOffset.UTC).toLocalDate()`. How much of that works in a JOQL query I don’t know. If all else fails, you may convert the first and the last timestamp of the current date to epoch timestamps and select events between them. — Ole V.V., Jan 06 '23 at 12:26
Lesson to learn: Do not store numeric timestamps in your database. Use `timestamp with time zone` data type. — Ole V.V., Jan 06 '23 at 12:26
Is there sill a way that somehow I will be able to use some SQL function which just convert the epoch to UTC time. I tried everything. And I know at the end if there were now choice, I have to use startTimestamp and endtimestamp — Mohit Kumar, Jan 06 '23 at 12:33
Does this answer your question? [How to select unix timestamp as date in Jooq?](https://stackoverflow.com/questions/60848890/how-to-select-unix-timestamp-as-date-in-jooq) Does this? [PostgreSQL: how to convert from Unix epoch to date?](https://stackoverflow.com/questions/16609722/postgresql-how-to-convert-from-unix-epoch-to-date) Also see [How to Convert a Unix Timestamp to a Date/Time Value in PostgreSQL](https://database.guide/how-to-convert-a-unix-timestamp-to-a-date-time-value-in-postgresql/). — Ole V.V., Jan 06 '23 at 14:56

score 1 · Answer 1 · answered Jan 06 '23 at 17:35

Can anybody help me regarding how to convert epoch time to LocalDate?

You can convert the epoch seconds into an Instant which can subsequently be converted into an OffsetDateTime or ZonedDateTime depending on your time-zone requirement. Finally, you can get the LocalDate out of the OffsetDateTime or ZonedDateTime using its .toLocalDate() function.

Note: The following demo uses ZoneOffset.UTC. You can use a different offset e.g. ZoneOffset.of("+05:30") to get an OffsetDateTime at this offset or a ZoneId e.g. ZoneId.of("Asia/Kolkata") to get a ZonedDateTime at this time-zone.

Demo:

import java.time.Instant;
import java.time.LocalDate;
import java.time.ZoneOffset;

class Main {
    public static void main(String[] args) {
        long epochSeconds = 1664584019;
        Instant instant = Instant.ofEpochSecond(epochSeconds);
        System.out.println(instant);

        // Get LocalDate out of Instant
        LocalDate date = instant.atOffset(ZoneOffset.UTC).toLocalDate();
        System.out.println(date);
    }
}

Output:

2022-10-01T00:26:59Z
2022-10-01

ONLINE DEMO

Note: Since you want to compare a LocalDate at UTC with curDate, the value of curDate should also be obtained at UTC i.e. curDate = LocalDate.now(ZoneOffset.UTC).

Learn more about the modern Date-Time API from Trail: Date Time.

But I need to do it in JPQL, it's creating weird timezone issues — Mohit Kumar, Jan 06 '23 at 18:32

Spring boot convert epoch timestamp to java.time.LocalDate?

1 Answers1