Skip site navigation (1) Skip section navigation (2)

Re: timestamp interval issue

From: Michael Glaesemann <grzm(at)seespotcode(dot)net>
To: Nis Jørgensen <nis(at)superlativ(dot)dk>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: timestamp interval issue
Date: 2007-10-08 13:37:19
Message-ID: 0FA3DD0A-3BA9-4F70-A2B1-5C3A7BAE39A8@seespotcode.net (view raw or flat)
Thread:
Lists: pgsql-novice
On Oct 8, 2007, at 6:14 , Nis Jørgensen wrote:

> Nis Jørgensen skrev:
>> SELECT *
>> FROM footable0
>> WHERE (date_created - interval '16 hours')::date = (now() -  
>> interval '16
>> hours')::date;
>>
>> Tested.
>
> Unfortunately, the performance is horrible - it is using a seqscan.

You can create a functional index to improve performance here,  
something like

create index rectified_created_at_idx on mytable (cast (created_at -  
interval '16 hours') to date);

I haven't thought it through, but you may run into issues with time  
zones -- this is not particular to this solution however.

Michael Glaesemann
grzm seespotcode net



In response to

pgsql-novice by date

Next:From: Bruce MomjianDate: 2007-10-08 20:46:26
Subject: Re: PG on NFS may be just a bad idea
Previous:From: Nis JørgensenDate: 2007-10-08 11:14:59
Subject: Re: timestamp interval issue

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group