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

Re: Picking out the most recent row using a time stamp column

From: Florian Weimer <fw(at)deneb(dot)enyo(dot)de>
To: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
Cc: "Dave Crooke" <dcrooke(at)gmail(dot)com>, "pgsql-performance" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Picking out the most recent row using a time stamp column
Date: 2011-02-26 20:54:50
Message-ID: 87r5auwm79.fsf@mid.deneb.enyo.de (view raw or flat)
Thread:
Lists: pgsql-performance
* Kevin Grittner:

> Dave Crooke <dcrooke(at)gmail(dot)com> wrote:
>  
>> create table data
>>    (id_key int,
>>     time_stamp timestamp without time zone,
>>     value double precision);
>> 
>> create unique index data_idx on data (id_key, time_stamp);
>  
>> I need to find the most recent value for each distinct value of
>> id_key.
>  
> Well, unless you use timestamp WITH time zone, you might not be able
> to do that at all.  There are very few places where timestamp
> WITHOUT time zone actually makes sense.

I don't think PostgreSQL keeps track of actual time zone values, just
as it doesn't keep track of the character encoding of TEXT columns.
Unless suppressed with WITHOUT TIME ZONE, PostgreSQL makes up some
time zone on demand.  This makes TIMESTAMP WITH TIME ZONE not that
useful, and it's often to use TIMESTAMP WITHOUT TIME ZONE with times
in UTC.

In response to

pgsql-performance by date

Next:From: Josh BerkusDate: 2011-02-26 21:06:00
Subject: Re: Picking out the most recent row using a time stamp column
Previous:From: Tom LaneDate: 2011-02-26 17:25:32
Subject: Re: Vacuum problem due to temp tables

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