Re: expression index on date_trunc
От | a.redhead@openinternetsolutions.com |
---|---|
Тема | Re: expression index on date_trunc |
Дата | |
Msg-id | 12664647.1546771201775901970.JavaMail.servlet@kundenserver обсуждение исходный текст |
Ответ на | expression index on date_trunc (a.redhead@openinternetsolutions.com) |
Список | pgsql-general |
><a.redhead@openinternetsolutions.com> writes: >> CREATE INDEX request_day_idx ON moksha_sm_request >(date_trunc('day', >request_received)); >... >> I'd be grateful if someone could point out what part of the statement >is not >> IMMUTABLE or how I could mark my create index statement as being >immutable. >date_trunc(timestamp with time zone) is not immutable because it >depends what >your current time zone is. That is, if you change what time zone you're >in a >timestamp with time zone could appear to change from one day to >another. >However date_trunc(timestamp without time zone) is immutable. So I >think what >you have to do is build your index on: >date_trunc('day', request_received AT TINE ZONE 'GMT') >or whatever time zone you're interested in. That will get you the day >part of >that timestamp at that time zone (because it first casts it to a >timestamp >without time zone for the time zone you specify). That worked perfectly, many thanks.
В списке pgsql-general по дате отправления: