17

I have the below query in a postgresql database

SELECT * 
FROM accounts 
where insertdate BETWEEN '2012-01-01' AND CURRENT_TIMESTAMP

So how can I replace the '2012-01-01' asking for the first day of the current year

There is one more issue. When I m having a new record in the account table the same moment is running the above select so it doesnt bring me the record I have just made.Is it reasonable? What is the best way to overtake it?

user1392203
  • 309
  • 3
  • 5
  • 13

2 Answers2

42

You're looking for date_trunc(), which can truncate a date to a specified precision (e.g. year, month, day):

SELECT date_trunc('year', now());

In your query:

SELECT * FROM accounts where insertdate BETWEEN 
date_trunc('year', now()) AND CURRENT_TIMESTAMP
beerbajay
  • 19,652
  • 6
  • 58
  • 75
  • It was perfect but I am having one more problem. When I m having a new record in the account table the same moment is running the above select so it doesnt bring me the record I have just made...What is the best way to overtake it? – user1392203 Jun 18 '12 at 13:46
  • If the operations are occurring in separate transactions, the query should find the new record as soon as the inserting transaction is committed. If they're in the same transaction, you just have to run the queries in the correct order. – beerbajay Jun 18 '12 at 14:00
  • is there a way to add 'current day 23:59:59' ? something like '2012-6-19 23:59:59' – user1392203 Jun 18 '12 at 14:16
  • I am ussing play framework play.db.jpa.GenericModel.JPAQuery and I am not sure what is happening behinde the code – user1392203 Jun 18 '12 at 14:32
  • Why are you writing a postgres query if you're going to issue it with JPA? You can pass a `Date` with the time field set as described in [this answer](http://stackoverflow.com/a/1908419/320220) – beerbajay Jun 18 '12 at 14:57
1

You can try and use this CURRENT_TIMESTAMP.YEAR-01-01

Rish
  • 447
  • 7
  • 24