I need to see only the current year rows from a table.
Would it be possible filter a timestamp column only by current year parameter, is there some function that can return this value?
SELECT * FROM mytable WHERE "MYDATE" LIKE CURRENT_YEAR
-
1Look at CURRENT_TIMESTAMP and then using date_part on it ... postgresql.org/docs/current/static/functions-datetime.htmlNeil Stockton– Neil Stockton2016-02-29 16:22:55 +00:00Commented Feb 29, 2016 at 16:22
Add a comment
|
3 Answers
In PostgreSQL you can use this:
SELECT * FROM mytable WHERE date_part('year', mydate) = date_part('year', CURRENT_DATE);
The date_part function is available in all PostgreSQL releases from current down to 7.1 (at least).
2 Comments
Jim Nasby
Note that that query will be pretty slow. A better idea would be to do mydate >= date_trunc('year',current_date) AND mydate < date_trunc('year',current_date + interval '1 year')
MyBrainHurts
Thanks a lot for that hint! Any improvement is very much appreciated. I tried to substantiate what you wrote with some measuring. I ran a test on a table containing about 200K records, some 50 columns overall and having 9 different columns of date or timestamp type. The number of records selected differed from 500 to 30K depending on the column. It took your queries up to 10% longer. The best result was near level. Of course I followed rules of statistical investigation. I guess you tried yourself many times. Someone out there who can come up with her or his own measuring?
This is pretty old, but now you can do:
SELECT date_part('year', now()); -> 2020
In the place of now you can pass any timestamp. More on Documentation
Comments
I prefer this writing
SELECT EXTRACT(YEAR FROM CURRENT_DATE) AS year;
So your query would be:
SELECT * FROM mytable WHERE "MYDATE" = EXTRACT(YEAR FROM CURRENT_DATE);
1 Comment
Kostas Nitaf
This does not provide an answer to the question. Once you have sufficient reputation you will be able to comment on any post; instead, provide answers that don't require clarification from the asker. - From Review
