0

I have a table in Postgre Database. The table has 2 timestamp format columns. I need those rows where one column timestamp is not equals the second column timestamp.

For the below query I need the Hibernate criteria restrictions.

select * from A where column1<>column2

Bhupesh Mathur
  • 85
  • 1
  • 11
  • You don't actually compare columns; you compare values of "fields". Does a value for a row in column1 or column2 change? Or is it set once and never changed? – Haris Osmanagić Nov 18 '19 at 11:25
  • Yes they are different values. In actual scenario they are createdTime and UpdatedTime. I need the rows where createdTime is not equal to UpdatedTime. – Bhupesh Mathur Nov 18 '19 at 11:31

2 Answers2

1

Did you try something like that?

CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery<Foo> cQuery = builder.createQuery(Foo.class);
Root<Foo> from = cQuery.from(Foo.class);
CriteriaQuery<Foo> select = cQuery.select(from);
select.where(builder.notEqual(from.get("col1"), from.get("col2")));
List<Foo> result = entityManager.createQuery(select).getResultList();
Eduard Romanyuk
  • 11
  • 1
  • 1
0

Given your comment here, you'll have to do a full table scan, which will create troubles with a bigger table. You will likely need to change your approach altogether, and introduce a field which says something like "IsUpdated". Or, when creating that record, UpdatedTime should be set to null, indicating it hasn't been updated yet. Then, you can do a query like "give all records where UpdatedTime is not null".

Haris Osmanagić
  • 1,249
  • 12
  • 28
  • Thanks Haris. This looks like a good approach. However, we already have the columns with the data in both the columns and hence we cannot use this approach. – Bhupesh Mathur Nov 20 '19 at 10:48
  • @Bhupesh Mathur Then you can simply update the records in your database, and use an approach which is fast enough. You can easily do some benchmarking to prove which approach is better and faster. – Haris Osmanagić Nov 21 '19 at 09:32