Re: Immutable way to cast timestamp TEXT to DATE? (for index)

On 1/4/19 2:21 PM, Ken Tanzer wrote:
> Hi.  I've got a text field in a table that holds this style of timestamp:
> 
> 2014-10-23T00:00:00
> 
> I'd like to be able to create an index on the date portion of this field 
> (as a date), because I have lots of queries that are searching for 
> particular dates or ranges.
> 
> I've tried various ways of getting to a date (::date, LEFT(x,10)::date, 
> etc.), but all of them throw the error "functions in index expression 
> must be marked IMMUTABLE."

?:
'2014-10-23T00:00:00'::timestamp

Can you also show the actual index expression?

> 
> Is there any way, hacky or otherwise, people know of to do this?  Thanks 
> in advance.
> 
> Ken
> 
> 
> -- 
> AGENCY Software
> A Free Software data system
> By and for non-profits
> /http://agency-software.org//
> /https://demo.agency-software.org/client/
> ken.tanzer@agency-software.org <mailto:ken.tanzer@agency-software.org>
> (253) 245-3801
> 
> Subscribe to the mailing list 
> <mailto:agency-general-request@lists.sourceforge.net?body=subscribe> to
> learn more about AGENCY or
> follow the discussion.


-- 
Adrian Klaver
adrian.klaver@aklaver.com