Re: Best practices: Handling Daylight-saving time

On 2005-03-11, Mike Benoit <ipso(at)snappymail(dot)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