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

Instead of spikes or dips, with your method customers will just be
confused as to why the labels skip an hour, or have two of the same
hour. It would make for a more accurate graph though, your right.

6 of 1, half a dozen of another I guess.


On Fri, 2005-03-11 at 23:33 +0000, Andrew - Supernews wrote:
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
--
Mike Benoit <ipso@snappymail.ca>