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+01Instead, 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 - [EMAIL PROTECTED] 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 _________________________________________________________ ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
