I've found a very interesting and short explanation of the issues faced when trying to create a PostgreSQL index using the date() function and how to resolve it. 
#postgresql #function #indexes #immutable #timezone #database
CC @PostgreSQLhttps://gist.github.com/cobusc/5875282
-
-
Replying to @pauloxnet @PostgreSQL
Wrong solution of course - better to use WHERE created_at >= '2020-02-01' AND created_at < '2020-02-02' (There are variations on this depending on timezone usage)
1 reply 0 retweets 2 likes -
Replying to @RhodiumToad @PostgreSQL
Thanks for your clarification. In my case I don't have a timestamp column, but a JSONB containing a date and a time on which I try to build an index or a generated column. I was trying to figure out if I could avoid writing a function as explained here:https://stackoverflow.com/a/29761984/755343 …
1 reply 1 retweet 1 like -
Replying to @pauloxnet @PostgreSQL
What format are your date and time values in inside the jsonb object?
1 reply 0 retweets 1 like -
Replying to @RhodiumToad @PostgreSQL
The JSONB column contains a lot of information and also something like this: {"date":"2020-01-01", "time":"12:30:00"}
1 reply 0 retweets 1 like -
Replying to @pauloxnet @PostgreSQL
ooh, nasty. Is it in some known timezone?
1 reply 0 retweets 2 likes -
Replying to @RhodiumToad @PostgreSQL
Unfortunately I haven't created the table and I can't remove the JSONB column , but I can add a generated column or an indexe. The timezone is always CET.
1 reply 0 retweets 1 like
Sounds like a function will be your best bet.
-
-
Replying to @RhodiumToad @PostgreSQL
I had hoped to be able to do without a function. Thanks for your help.
0 replies 0 retweets 1 likeThanks. Twitter will use this to make your timeline better. UndoUndo
-
Loading seems to be taking a while.
Twitter may be over capacity or experiencing a momentary hiccup. Try again or visit Twitter Status for more information.