Re: Date trunc in UTC - Mailing list pgsql-sql

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' ANDmonth = 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@electronic-group.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
_________________________________________________________