0

I spent almost a day trying to fetch bottom 10 rows from a table that has few hundred thousand rows in multiple ways. But timestamp in result set is always messed up by 7 hours (that is the time difference between UTC and my local)

Schema

CREATE TABLE IF NOT EXISTS xyz(
  id timestamp NOT NULL, 
  name varchar(40) NOT NULL, 
  PRIMARY KEY (id,name )
);

Bottom 10 entries in db after running select * from xyz order by id desc limit 10;

 2020-05-12 12:00:00+00  
 2020-05-12 12:00:00+00  
 2020-05-12 11:59:00+00  
 2020-05-12 11:58:00+00  
 2020-05-12 11:58:00+00  
 2020-05-12 11:58:00+00  
 2020-05-12 11:57:00+00  
 2020-05-12 11:56:00+00  
 2020-05-12 11:56:00+00  
 2020-05-12 11:55:00+00

I tried following 3 ways of selecting bottom 10 rows from DB. Also I always get null list if I select within a range. 

List<xyz> findTop10ByOrderByIdDesc();
List<xyz> findTop10DistinctByIdBetweenOrderByIdDesc(LocalDateTime now,LocalDateTime after);
List<xyz> findTop10DistinctByIdBetweenOrderByIdDesc(Date now,Date after);

Here is POJO for holding the result set of above query

public class xyz{
  @Id
  @GeneratedValue(strategy = GenerationType.AUTO)
  private java.util.Date id;
//private LocalDateTime id
  private String name;

// ...
}

but id field is always off by 7 hours (for example: 1st element I get is 2020-05-12 05:00:00) . a)How can I fix my code to get right timestamp in POJO (or result set). b) How can I retrieve all the entries within a range (I get null list now). I think both of them are related

Ole V.V.
  • 81,772
  • 15
  • 137
  • 161
mobileDev
  • 1,358
  • 2
  • 14
  • 34
  • Can you replace the `Date` with an `Instant`? I'm hoping this would increase the chance of it getting handled correctly. – Jens Schauder May 15 '20 at 09:45
  • What's wrong? If I understand correctly, 05:00 in your time zone *is* the same time as 12:00 UTC, so you *are* getting the correct time? What am I missing? – Ole V.V. May 17 '20 at 05:46
  • While the modern `Instant` is better than the poorly designed and long outdated `Date`, I don't think you should use either for an entity ID. `timestamp` in the database neither. How about an auto-incremented ID, for example? – Ole V.V. May 17 '20 at 05:49
  • Which database engine are you using? MySQL? If so, the timestamp in the database is in UTC, and if `Instant` doesn’t work in the entity class, you should probably use `OffsetDateTime`. – Ole V.V. May 17 '20 at 06:49
  • @JensSchauder: if you can post it as answer, I will accept it. Util.Date should not be used at all (java.time is way to go) – mobileDev May 17 '20 at 17:25
  • 1
    @OleV.V. I am using postgres. I solved it by a)using ```Instant``` instead of ```Date``` and b) parsing timestamp exactly in the format as in DB as https://stackoverflow.com/questions/61846147/parsing-timestamp-with-into-date-or-instant-in-java?noredirect=1#comment109392288_61846147 – mobileDev May 17 '20 at 17:28
  • @OleV.V. Good advice about NOT using timestamp as entity ID. But this is third party database that sits outside my network (and I issue fetch only queries) – mobileDev May 17 '20 at 17:30
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/214070/discussion-between-ole-v-v-and-mobiledev). – Ole V.V. May 17 '20 at 18:51

1 Answers1

0

java.util.Date which technically according to the documentation represents a point in time. But in reality it is a an awful mixture that gets used for all kinds of stuff. This results in all kind of weird (and probably plain wrong) stuff happening in the database, the driver and any ORM layers on top of that.

Luckily java.util.Date now hasa proper replacement in the java.time.* package. You are seem to be interested in a point of time: When did an event happen. The correct data type for that is [Instant][1].

While I can't promise anything the chances of that being persisted and loaded in the correct way is much bigger.

Jens Schauder
  • 77,657
  • 34
  • 181
  • 348