2019-12-01 A simple trick with selecting rows created today

For today, I only have a short but useful (at least for me) trick. When I test some new feature involving saving something to the database (PostgreSQL in my case), I often want to see what was actually written into the database. For that purpose, the best query is a short one. Since most of the tables involved have a created column of type TIMESTAMP (which contain, well, the moment the record was created, in UTC), I could of course do

SELECT *
FROM TABLE ordered BY created DESC
LIMIT 1;

but this might not be what I want - what if I want to see more than one record I added recently? Instead of guessing how many of them I need, why not display everything that was created today?

SELECT *
FROM TABLE
WHERE created > CURRENT_DATE::TIMESTAMP;

Here is what happens: CURRENT_DATE is the date, and casting it to TIMESTAMP type just adds the midnight to the date. This way, I select everything that was created today, no matter how many rows (“today” in the UTC sense). (It helps a lot that the local time in Poland is never off more than two hours from UTC, of course.)

I’m not sure how useful it is for anyone but me, but I thought it is nice (and simple) enough to have in one’s toolbox just in case someone needs it one day.

CategoryEnglish, CategoryBlog, CategoryPostgreSQL