Re: Date trunc in UTC

Hi Richard

Ok, I'll do my best to explain clearer ;)

I have to make some monthly reports about some service requests
activity. So, I'm keeping in a table the monthly traffic.

TABLE traffic
+---------+------------------------+--------+
| service | month | visits |
+---------+------------------------+--------+
| chat | 2002-11-01 00:00:00+01 | 37002 |
| video | 2002-11-01 00:00:00+01 | 186354 |
| chat | 2002-10-01 00:00:00+01 | 41246 |
| video | 2002-10-01 00:00:00+01 | 86235 |

So, when I have a new visit on any service, I increase the counter for
that month. The problems are:

- As you see, the month includes timezone information (+01), which
corresponds to the CET beggining of the month.

- Whenever a new month starts, I have to create a new entry in the table.

I have done a plpgsql procedure 'increase_counter' that increases the
counter 'visits = visits + 1' every time it gets called. But, I have to
check if I went into the next month, so basically I do

UPDATE traffic SET visits = visits + 1 WHERE service = 'chat' AND
month = DATE_TRUNC (''month'', ''now''::timestamp);

If there was no row updated, then I create the new entry as

INSERT INTO traffic VALUES
('chat', DATE_TRUNC (''month'', ''now''::timestamp), 1);

So, as I can see in the traffic table, the DATE_TRUNC is, in fact,
equivalent to

2002-11-01 00:00:00+01 (CET) == 2002-09-30 23:00:00+00 (UTC)

If we think that I will work in an international environment, I would
rather to have in the table as the result of the DATE_TRUNC the right
UTC value, so, the right begginning of the month in UTC.

2002-11-01 00:00:00+00 (UTC)

So, if I'm working in the CET timezone, what I would like to see is

2002-11-01 01:00:00+01 (CET)

Or, if I'm working with another time zone,

2002-10-31 16:00:00-08 (dunno timezone name)

TABLE traffic
+---------+------------------------+--------+
| service | month | visits |
+---------+------------------------+--------+
| chat | 2002-11-01 01:00:00+01 | 37002 |
| video | 2002-11-01 01:00:00+01 | 186354 |
| chat | 2002-10-01 01:00:00+01 | 41246 |
| video | 2002-10-01 01:00:00+01 | 86235 |

In fact, DATE_TRUNC is returning the beggining of the month FOR THE
WORKING TIME ZONE, but I need to know, in my timezone, what is the
begginning of the UTC month.

Another more problem is that if I set the time zone in the session, I'm
not able to recover to its previous state. In plpgsql,

client preferences -> SET TIME ZONE 'PST8PDT';

... calling to my wrapper function

CREATE FUNCTION date_trunc_utc (TEXT, TIMESTAMP) RETURN TIMESTAMP AS '
DECLARE
st_month TIMESTAMP;
BEGIN
SET TIME ZONE ''UTC'';
st_month = DATE_TRUNC ($1, $2);
RESET TIME ZONE;
END
' LANGUAGE 'plpgsql';

-> SHOW TIME ZONE
NOTICE: Time zone is 'CET'

so basically, I cannot change to UTC because I'm not able no more to
recover to the client timezone preferences.

I hope I explained well ;)

Thanks for everything

Richard Huxton wrote:
> On Wednesday 20 Nov 2002 10:37 am, Thrasher wrote:
>
>>No I cannot use SET TIME ZONE.
>>
>>SET TIME ZONE will be set by any client backend. But what I want to get
>>is that DATE_TRUNC('month', <now in timezone +1>) = DATE_TRUNC('month',
>><now in timezone -7>).
>
>
> Sorry, I've obviously misunderstood. Are you just looking to discard the
> timezone so they look the same?
>
> select date_trunc('month', CAST(CURRENT_TIMESTAMP AS timestamp without time
> zone));
> date_trunc
> ---------------------
> 2002-11-01 00:00:00
>
> I'd have thought that would give you some problems around local/utc midnight
> on the first of the month.
>
> Or is it that you want to know what time it was in UTC zone at the start of
> the month local time?
>
> If I'm still being a bit slow (quite likely) can you explain what you're using
> this for?
>
>
>>>>=# select date_trunc ('month', now ());
>>>> date_trunc
>>>>------------------------
>>>> 2002-11-01 00:00:00+01
>>>
>
>>>>Instead, I would like to have as a result
>>>>
>>>> 2002-11-01 01:00:00+01
>>>>
>>>>which is correct, but I cannot set the whole server to UTC. Any way to
>>>>get this ?
>>>
>

--
Juan A. FERNANDEZ-REBOLLOS - jfernandez(at)electronic-group(dot)com
Mobile Dept.
_________________________________________________________

ELECTRONIC GROUP INTERACTIVE - www.electronic-group.com
World Trade Center, Moll de BARCELONA
Edificio Norte 4 Planta
08039 BARCELONA SPAIN
Tel : +34 93600 23 23 Fax : +34 93600 23 10
_________________________________________________________