Re: Best practices: Handling Daylight-saving time - Mailing list pgsql-general

On 2005-03-11, Mike Benoit <ipso@snappymail.ca> wrote:
> Here is a scenario I ran in to with collecting bandwidth usage and
> displaying it back in graph form to customers.
>
> You can store the timestamps in GMT, but the customer wants to see when
> spikes happen in his localtime, which most likely has DST. So twice a
> year, you are either compressing two hours of bandwidth usage into one,
> or the opposite, stretching one hour in to two, which of course produces
> somewhat odd looking graphs during that time.

That seems an odd way to handle it. If you graph the data by days according
to the customer's time, then on one day in the year your graph is one hour
smaller, and on another day it is one hour larger. The point to notice is
that the customer's local time should affect only the _labels_ on the graph,
and possibly your choice of start and end times, and not the _data_ being
plotted.

For example, suppose I have a table:

create table tztst (ts timestamptz primary key, value float8 not null);

and I want to plot individual days from it in the customer's timezone:

test=> set timezone to 'America/Denver';  -- or wherever he is
SET

test=> select ts::time,value from tztst
 where ts between '2005-04-02 00:00:00' and '2005-04-02 23:59:59' order by ts;
    ts    |      value
----------+------------------
 00:00:00 | 286.764410064167
 01:00:00 | 291.294525072763
 02:00:00 | 294.912455364789
 03:00:00 | 297.582051776698
 04:00:00 | 299.276640583591
 05:00:00 | 299.979290014267
 06:00:00 |  299.68297942788
 07:00:00 | 298.390669461862
 08:00:00 | 296.115272450212
 09:00:00 | 292.879523407724
 10:00:00 | 288.715752869235
 11:00:00 | 283.665563853606
 12:00:00 | 277.779416180109
 13:00:00 | 271.116122290598
 14:00:00 | 263.742259615024
 15:00:00 | 255.731505351766
 16:00:00 |  247.16390030942
 17:00:00 | 238.125049165494
 18:00:00 | 228.705265132773
 19:00:00 | 218.998667579544
 20:00:00 | 209.102241619985
 21:00:00 |  199.11486907096
 22:00:00 | 189.136340457592
 23:00:00 | 179.266357939324
(24 rows)

test=> select ts::time,value from tztst
 where ts between '2005-04-03 00:00:00' and '2005-04-03 23:59:59' order by ts;
    ts    |      value
----------+------------------
 00:00:00 | 169.603539118895
 01:00:00 | 160.244431687857
 03:00:00 | 151.282548753949
 04:00:00 | 142.807434489044
 05:00:00 | 134.903769433375
 06:00:00 | 127.650524395576
 07:00:00 | 121.120171402458
 08:00:00 | 115.377959582483
 09:00:00 | 110.481263218032
 10:00:00 | 106.479008480546
 11:00:00 | 103.411184576393
 12:00:00 | 101.308444187935
 13:00:00 |  100.19179720206
 14:00:00 | 100.072400786337
 15:00:00 | 100.951447910284
 16:00:00 | 102.820155425614
 17:00:00 | 105.659851824544
 18:00:00 | 109.442163799338
 19:00:00 | 114.129299739007
 20:00:00 | 119.674427330605
 21:00:00 | 126.022141492211
 22:00:00 | 133.109017962198
 23:00:00 | 140.864247013488
(23 rows)

test=> select ts::time,value from tztst
 where ts between '2005-10-30 00:00:00' and '2005-10-30 23:59:59' order by ts;
    ts    |      value
----------+------------------
 00:00:00 | 110.349122831853
 01:00:00 | 114.741289638094
 01:00:00 | 119.837588745288
 02:00:00 | 125.595930978012
 03:00:00 | 131.968759497219
 04:00:00 | 138.903442561358
 05:00:00 | 146.342708199957
 06:00:00 | 154.225117209803
 07:00:00 | 162.485570567354
 08:00:00 | 171.055847066766
 09:00:00 | 179.865166743321
 10:00:00 | 188.840775429059
 11:00:00 | 197.908545612907
 12:00:00 |  206.99358864294
 13:00:00 | 216.020873214721
 14:00:00 | 224.915845037786
 15:00:00 | 233.605042562575
 16:00:00 | 242.016703682664
 17:00:00 | 250.081358401684
 18:00:00 | 257.732402570221
 19:00:00 | 264.906647954345
 20:00:00 | 271.544844092858
 21:00:00 | 277.592167633387
 22:00:00 | 282.998675105977
 23:00:00 |  287.71971539486
(25 rows)

All of these can be converted to meaningful (and un-distorted) graphs.

--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services