From: Juan Fernandez <jfernandez@electronic-group.com>
To: Richard Huxton <dev@archonet.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Date trunc in UTC
Date: Wed, 20 Nov 2002 16:40:50 +0100
Message-ID: <3DDBAD02.3090406@electronic-group.com> (raw)
References: <3DDB5991.9060706@fibers.upc.es>
<200211201024.17210.dev@archonet.com>
<3DDB65ED.70101@fibers.upc.es>
<200211201306.47345.dev@archonet.com>
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@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
_________________________________________________________
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jfernandez@electronic-group.com, dev@archonet.com
Subject: Re: Date trunc in UTC
In-Reply-To: <3DDBAD02.3090406@electronic-group.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox