Re: Date trunc in UTC
От | Juan Fernandez |
---|---|
Тема | Re: Date trunc in UTC |
Дата | |
Msg-id | 3DDBAD02.3090406@electronic-group.com обсуждение исходный текст |
Ответ на | Date trunc in UTC (Thrasher <thrasher@fibers.upc.es>) |
Список | 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 _________________________________________________________
В списке pgsql-sql по дате отправления: