Обсуждение: timestamp <-> ctime conversion question...

Hi,

i'm trying to convert time stamps to "seconds since epoch" and back. My
original timestamps are given with a time zone (UTC), and i have a
conversion function to "ctime" which works pretty well:

CREATE OR REPLACE FUNCTION to_ctime (timestamptz) RETURNS integer AS $$
         SELECT date_part('epoch', $1)::integer;
$$ LANGUAGE SQL;

test=# select  to_ctime('1970-01-01T00:00Z'); to_ctime
----------
         0
(1 row)


However, i fail at converting those ctime values back into timestamps with
time zone UTC. Inspired from the query on the date/time docs pages, i've
tried the following approaches:

test=# SELECT (TIMESTAMP WITH TIME ZONE 'epoch'
                 + 0 * INTERVAL '1 second') AT TIME ZONE 'UTC';

       timezone
---------------------
  1970-01-01 00:00:00
(1 row)

This would yield the right timestamp, but loses the time zone. The nex approach:

test=# SELECT (TIMESTAMP WITHOUT TIME ZONE 'epoch'
                 + 0 * INTERVAL '1 second') AT TIME ZONE 'UTC';
         timezone
------------------------
  1970-01-01 01:00:00+01
(1 row)

yields the right timestamp (from an absolute point of view) as well, but in
the wrong (my local) timezone. My next approach:

test=# select timezone('UTC', timestamp with time zone 'epoch' + 0 *
INTERVAL '1 second');
       timezone
---------------------
  1970-01-01 00:00:00
(1 row)

loses the time zone as well. I'm a bit reluctant to use tricks like manually
appending the "Z" as literal text so that it would "look like" a valid UTC
time stamp.

I'd appreciate any insight on this - am i simply missing something? I'm
using PostgreSQL 8.1.0, if that matters.

thanks & cheers

--
Alex Mayrhofer <axelm (at) nona.net>
http://nona.net/features/map/

On Tue, Dec 13, 2005 at 05:31:49PM +0100, Alex Mayrhofer wrote:
> i'm trying to convert time stamps to "seconds since epoch" and back. My
> original timestamps are given with a time zone (UTC), and i have a
> conversion function to "ctime" which works pretty well:
>
> CREATE OR REPLACE FUNCTION to_ctime (timestamptz) RETURNS integer AS $$
>         SELECT date_part('epoch', $1)::integer;
> $$ LANGUAGE SQL;
>
> test=# select  to_ctime('1970-01-01T00:00Z'); to_ctime
> ----------
>         0
> (1 row)
>
>
> However, i fail at converting those ctime values back into timestamps with
> time zone UTC.

According to the Date/Time Types documentation,

    All timezone-aware dates and times are stored internally in UTC.
    They are converted to local time in the zone specified by the
    timezone configuration parameter before being displayed to the
    client.

As far as I know there isn't a way to defeat this.  However, the
developers' TODO file does have the following item:

    Allow TIMESTAMP WITH TIME ZONE to store the original timezone
    information, either zone name or offset from UTC

Presumably this would allow timestamps to be displayed with a
timezone other than the current setting.

If you don't mind having the timestamp as a text value (which you
could cast to timestamptz, albeit with a loss of the desired time
zone) then you could try something like this:

CREATE FUNCTION settz(tz text, ts timestamptz) RETURNS text AS $$
DECLARE
    savetz  text;
    retval  text;
BEGIN
    savetz := current_setting('TimeZone');
    PERFORM set_config('TimeZone', tz, true);
    retval := ts;
    PERFORM set_config('TimeZone', savetz, true);
    RETURN retval;
END;
$$ LANGUAGE plpgsql STABLE STRICT;

Examples:

test=> SELECT now();
              now
-------------------------------
 2005-12-13 10:20:54.109306-07
(1 row)

test=> SELECT settz('UTC', now());
             settz
-------------------------------
 2005-12-13 17:20:54.109306+00
(1 row)

test=> SELECT settz('UTC', now())::timestamptz;
             settz
-------------------------------
 2005-12-13 10:20:54.109306-07
(1 row)

--
Michael Fuhr

Alex Mayrhofer <axelm@nona.net> writes:
> i'm trying to convert time stamps to "seconds since epoch" and back.

> test=# SELECT (TIMESTAMP WITH TIME ZONE 'epoch'
>                  + 0 * INTERVAL '1 second') AT TIME ZONE 'UTC';

You're overthinking the problem.  It should be just

    SELECT (TIMESTAMP WITH TIME ZONE 'epoch' + n * INTERVAL '1 second')

There is a built-in function to_timestamp() equivalent to this in 8.1,
though it doesn't seem to have made it into the documentation :-(

            regards, tom lane

On Tue, Dec 13, 2005 at 10:28:42AM -0700, Michael Fuhr wrote:

> According to the Date/Time Types documentation,
>
>     All timezone-aware dates and times are stored internally in UTC.
>     They are converted to local time in the zone specified by the
>     timezone configuration parameter before being displayed to the
>     client.
>
> As far as I know there isn't a way to defeat this.  However, the
> developers' TODO file does have the following item:
>
>     Allow TIMESTAMP WITH TIME ZONE to store the original timezone
>     information, either zone name or offset from UTC
>
> Presumably this would allow timestamps to be displayed with a
> timezone other than the current setting.
*Display* of timestamptz values at arbitrary time zones is
already possible using the "at time zone" syntax. Retrieving
the "original" (as in "as inserted"), however, isn't
possible to date. It would certainly be very useful. In
GNUmed we are currently solving this by explicitely storing
the original timestamp via a trigger on insert. I wonder
whether a good intermediate solution would be to have an
"officially supported" (whatever that means) composite
type ?

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

On Tue, Dec 13, 2005 at 08:21:20PM +0100, Karsten Hilbert wrote:
> On Tue, Dec 13, 2005 at 10:28:42AM -0700, Michael Fuhr wrote:
> > As far as I know there isn't a way to defeat this.  However, the
> > developers' TODO file does have the following item:
> >
> >     Allow TIMESTAMP WITH TIME ZONE to store the original timezone
> >     information, either zone name or offset from UTC
> >
> > Presumably this would allow timestamps to be displayed with a
> > timezone other than the current setting.
>
> *Display* of timestamptz values at arbitrary time zones is
> already possible using the "at time zone" syntax.

But the result is a timestamp without time zone and thus doesn't
display the target time zone.  In other words, you can display a
timestamptz *at* another time zone but not *with* another time zone.
That's what I was talking about, and that's what I think the OP
wants.

--
Michael Fuhr

On Tue, Dec 13, 2005 at 12:34:19PM -0700, Michael Fuhr wrote:

> > *Display* of timestamptz values at arbitrary time zones is
> > already possible using the "at time zone" syntax.
>
> But the result is a timestamp without time zone and thus doesn't
> display the target time zone.  In other words, you can display a
> timestamptz *at* another time zone but not *with* another time zone.
Ah, OK, there's a subtle difference, true.

However, if in my code I am able to say "at time zone ..." I
*already* know the time zone. Can't I then just *add* it to
the value I am getting back from the database ?

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

On Dec 14, 2005, at 2:49 , Tom Lane wrote:

> There is a built-in function to_timestamp() equivalent to this in 8.1,
> though it doesn't seem to have made it into the documentation :-(

It's in 9.8. Data Type Formatting Functions
http://www.postgresql.org/docs/current/interactive/functions-
formatting.html

Michael Glaesemann
grzm myrealbox com

Michael Glaesemann <grzm@myrealbox.com> writes:
> On Dec 14, 2005, at 2:49 , Tom Lane wrote:
>> There is a built-in function to_timestamp() equivalent to this in 8.1,
>> though it doesn't seem to have made it into the documentation :-(

> It's in 9.8. Data Type Formatting Functions

Yeah, I found it later.  That's about as random a placement as one can
imagine, however --- this is by no possible classification a formatting
function, even if it was rather stupidly given the same name as
something that is a formatting function.  I'm thinking about moving the
entry to where I expected to find it (under Date/Time Functions and
Operators).

            regards, tom lane

On Tue, Dec 13, 2005 at 08:21:20PM +0100, Karsten Hilbert wrote:
> > Presumably this would allow timestamps to be displayed with a
> > timezone other than the current setting.
> *Display* of timestamptz values at arbitrary time zones is
> already possible using the "at time zone" syntax. Retrieving
> the "original" (as in "as inserted"), however, isn't
> possible to date. It would certainly be very useful. In

Actually, a while ago I wrote a module that would allow you to store
arbitrary tags with normal values and it would remember them. One of
the applications I thought of was such a type:

test=# select t, t + interval '2 hours' from timestamp_test offset 2
limit 1;
                   t                   |               ?column?
---------------------------------------+---------------------------------------
 2005-08-14 02:00:00+02 Asia/Hong_Kong | 2005-08-14 04:00:00+02 Asia/Hong_Kong
(1 row)

So it's displaying the timezone as my current time (UTC+1) but
remembered the timezone I supplied. To make it truly useful you'd have
to go though and create all the operators and probably come up with a
default output format. Something like:

select value(t) at time zone tag(t) from timestamp_test;

Would display the time in the timezone given. To answer the question
about why not use "timestamp" as the base type, it's to distinguish
daylight savings time. Another method would be to store an integer
(seconds since epoch) and the timezone as a pair.

http://svana.org/kleptog/pgsql/taggedtypes.html

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Martijn van Oosterhout wrote:
> On Tue, Dec 13, 2005 at 08:21:20PM +0100, Karsten Hilbert wrote:
>>> Presumably this would allow timestamps to be displayed with a
>>> timezone other than the current setting.
>> *Display* of timestamptz values at arbitrary time zones is
>> already possible using the "at time zone" syntax. Retrieving
>> the "original" (as in "as inserted"), however, isn't
>> possible to date. It would certainly be very useful. In
>
> Actually, a while ago I wrote a module that would allow you to store
> arbitrary tags with normal values and it would remember them. One of
> the applications I thought of was such a type:

Hi,

thanks to all for your helpful suggestions - i have now decided to go with
changing the timezone in the postgres installation to UTC - that seems to be
the best solution to me.

thanks,

Alex

I'm having a problem converting a simple date routing to PLPGSQL.  I
know the error has to be something stupid but I'm missing it.  The error
is in the "ExpireDate := (date 'StartDate' + integer 'NumOfDays');" line
in the procedure below.  I didn't understand why I had to convert the
date to a string and back to a date but ok.  Below is the error I'm
getting a test and please tell me where I'm going wrong.  Also I include
the trigger procedure, trigger and insert.  Please remember that I'm a
MS-SQL guy migrating to PostgreSQL.

ERROR:  invalid input syntax for type date: "StartDate"
CONTEXT:  SQL statement "SELECT  (date 'StartDate' + integer
'NumOfDays')"
PL/pgSQL function "TF_BannerAd_Ads_InsMod" line 8 at assignment


CREATE OR REPLACE FUNCTION "TF_BannerAd_Ads_InsMod"()
  RETURNS "trigger" AS
$BODY$
DECLARE
    ExpireDate timestamptz;    -- Date the Banner Ad will expire.
    StartDate char(10);    -- Date the Banner Ad was created or
renewed
    NumOfDays char(10);    -- Number of Dates the Ad will be in
place.
BEGIN
    StartDate := to_char(NEW.bannerad_creationdate, 'YYYY-MM-DD');
    NumOfDays := to_char(NEW.bannerad_term, '99999');
    ExpireDate := (date 'StartDate' + integer 'NumOfDays');
    IF (TG_OP = 'UPDATE') THEN
        UPDATE public.bannerad_ads SET bannerad_expiredate =
ExpireDate WHERE bannerad_id = OLD.bannerad_id;
    ELSIF (TG_OP = 'INSERT') THEN
        UPDATE public.bannerad_ads SET bannerad_expiredate =
ExpireDate WHERE bannerad_id = NEW.bannerad_id;
    END IF;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

CREATE TRIGGER "Trg_BannerAd_Ads_InsMod" BEFORE INSERT OR UPDATE
   ON bannerad_ads FOR EACH ROW
   EXECUTE PROCEDURE public."TF_BannerAd_Ads_InsMod"();

insert into bannerad_ads
(bannerad_href,bannerad_alttext,bannerad_filename,bannerad_creationdate,
bannerad_term) values ('http://www.equilt.com','ElectricQuilt Southern
Music','EQMBannerAd4.gif','2006-01-20 01:00:00-05',18250);


--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.22/238 - Release Date:
1/23/2006

On Jan 25, 2006, at 9:45 , Foster, Stephen wrote:

> ERROR:  invalid input syntax for type date: "StartDate"
> CONTEXT:  SQL statement "SELECT  (date 'StartDate' + integer
> 'NumOfDays')"
> PL/pgSQL function "TF_BannerAd_Ads_InsMod" line 8 at assignment

Why are you single-quoting StartDate and NumOfDays? Single-quotes are
generally used for string literals (e.g., text and varchar types). If
you are trying to preserve the case of the variable name, you need to
(always) use double-quotes, including when you define them.

Hope this helps a bit. I haven't looked through all of the code, so
there may be other bugs lurking, but this is definitely one of the
problems.

Michael Glaesemann
grzm myrealbox com

Michael, I tried that line in the trigger procedure with double quotes,
single quotes and without.  The only way it would save was with single
quotes and that is why you saw it that way.  I know it has to be some
sort of stupid syntax error but since I'm new to PostgreSQL (as far as
this level of coding) I have no idea what my error is.

If I try to save without which was what I did in the first place I
receive this using "ExpireDate := (date StartDate + integer NumOfDays);"

ERROR:  syntax error at or near "$1" at character 16
QUERY:  SELECT  (date  $1  + integer  $2 )
CONTEXT:  SQL statement in PL/PgSQL function "TF_BannerAd_Ads_InsMod"
near line 8

Full procedure again:

CREATE OR REPLACE FUNCTION "TF_BannerAd_Ads_InsMod"()
  RETURNS "trigger" AS
$BODY$
DECLARE
    ExpireDate timestamptz;    -- Date the Banner Ad will expire.
    StartDate char(10);    -- Date the Banner Ad was created or
renewed
    NumOfDays char(10);    -- Number of Dates the Ad will be in
place.
BEGIN
    StartDate := to_char(NEW.bannerad_creationdate, 'YYYY-MM-DD');
    NumOfDays := to_char(NEW.bannerad_term, '99999');
    ExpireDate := (date StartDate + integer NumOfDays);
    IF (TG_OP = 'UPDATE') THEN
        UPDATE public.bannerad_ads SET bannerad_expiredate =
ExpireDate WHERE bannerad_id = OLD.bannerad_id;
    ELSIF (TG_OP = 'INSERT') THEN
        UPDATE public.bannerad_ads SET bannerad_expiredate =
ExpireDate WHERE bannerad_id = NEW.bannerad_id;
    END IF;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

The hard bits I'm getting easily but I keep getting hung up over simple
junk.

Thanks for the help,

Lee Foster/


-----Original Message-----
From: Michael Glaesemann [mailto:grzm@myrealbox.com]
Sent: Tuesday, January 24, 2006 6:58 PM
To: Foster, Stephen
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Setting expire date on insert/modify


On Jan 25, 2006, at 9:45 , Foster, Stephen wrote:

> ERROR:  invalid input syntax for type date: "StartDate"
> CONTEXT:  SQL statement "SELECT  (date 'StartDate' + integer
> 'NumOfDays')"
> PL/pgSQL function "TF_BannerAd_Ads_InsMod" line 8 at assignment

Why are you single-quoting StartDate and NumOfDays? Single-quotes are
generally used for string literals (e.g., text and varchar types). If
you are trying to preserve the case of the variable name, you need to
(always) use double-quotes, including when you define them.

Hope this helps a bit. I haven't looked through all of the code, so
there may be other bugs lurking, but this is definitely one of the
problems.

Michael Glaesemann
grzm myrealbox com



--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.22/238 - Release Date:
1/23/2006


--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.22/238 - Release Date:
1/23/2006

On Jan 25, 2006, at 10:12 , Foster, Stephen wrote:

> If I try to save without which was what I did in the first place I
> receive this using "ExpireDate := (date StartDate + integer
> NumOfDays);"
>
> ERROR:  syntax error at or near "$1" at character 16
> QUERY:  SELECT  (date  $1  + integer  $2 )
> CONTEXT:  SQL statement in PL/PgSQL function "TF_BannerAd_Ads_InsMod"
> near line 8

The  common ways of casting in PostgreSQL are some_value::some_type,
e.g., "ExpireDate"::date, or using CAST, e.g., CAST ("ExpireDate" as
date). In the general case, some_type some_value will not work.

The docs can also be very helpful. I myself just looked up the syntax
of the CAST expression (I generally use the double-colon method).

See if that makes a difference.

Michael Glaesemann
grzm myrealbox com

On Tue, 24 Jan 2006, Foster, Stephen wrote:

> CREATE OR REPLACE FUNCTION "TF_BannerAd_Ads_InsMod"()
>   RETURNS "trigger" AS
> $BODY$
> DECLARE
>     ExpireDate timestamptz;    -- Date the Banner Ad will expire.
>     StartDate char(10);    -- Date the Banner Ad was created or
> renewed
>     NumOfDays char(10);    -- Number of Dates the Ad will be in
> place.
> BEGIN
>     StartDate := to_char(NEW.bannerad_creationdate, 'YYYY-MM-DD');
>     NumOfDays := to_char(NEW.bannerad_term, '99999');
>     ExpireDate := (date 'StartDate' + integer 'NumOfDays');

I think something like:

ExpireDate := CAST(NEW.bannerad_creationdate as Date) + NEW.bannerad_term;

may work for you.

Thanks guys it finally worked correctly.  Just in case someone else get
hung on this type of thing here is the working trigger function.

CREATE OR REPLACE FUNCTION "TF_BannerAd_Ads_InsMod"()
  RETURNS "trigger" AS
$BODY$
DECLARE
    ExpireDate timestamptz;    -- Date the Banner Ad will expire.
BEGIN
    NEW.bannerad_expiredate := (NEW.bannerad_creationdate::date +
NEW.bannerad_term::int4);
    RETURN NEW;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Stephan Szabo
Sent: Tuesday, January 24, 2006 7:18 PM
To: Foster, Stephen
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Setting expire date on insert/modify

On Tue, 24 Jan 2006, Foster, Stephen wrote:

> CREATE OR REPLACE FUNCTION "TF_BannerAd_Ads_InsMod"()
>   RETURNS "trigger" AS
> $BODY$
> DECLARE
>     ExpireDate timestamptz;    -- Date the Banner Ad will expire.
>     StartDate char(10);    -- Date the Banner Ad was created or
> renewed
>     NumOfDays char(10);    -- Number of Dates the Ad will be in
> place.
> BEGIN
>     StartDate := to_char(NEW.bannerad_creationdate, 'YYYY-MM-DD');
>     NumOfDays := to_char(NEW.bannerad_term, '99999');
>     ExpireDate := (date 'StartDate' + integer 'NumOfDays');

I think something like:

ExpireDate := CAST(NEW.bannerad_creationdate as Date) +
NEW.bannerad_term;

may work for you.


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to majordomo@postgresql.org so that your
       message can get through to the mailing list cleanly

--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.22/238 - Release Date:
1/23/2006


--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.22/238 - Release Date:
1/23/2006

"Foster, Stephen" <stephenlfoster@comcast.net> writes:
> Michael, I tried that line in the trigger procedure with double quotes,
> single quotes and without.  The only way it would save was with single
> quotes and that is why you saw it that way.

Just for reference, the thing that was tripping you up (or one thing
that was tripping you up at least) was trying to extrapolate the syntax

    typename 'literal value'

to situations where you didn't mean an actual literal constant.  This
syntax is something that's forced on us by the SQL standard, but we do
not support it for anything but literal constants (which is all that the
standard defines it for, either).  In the examples you gave, you wanted
to coerce the value of a variable of one type to some other type, and so
you should have written either

    CAST( variablename AS typename )
or
    variablename :: typename

the former being the SQL-spec syntax for a runtime type conversion
and the latter being a traditional Postgres abbreviation.

            regards, tom lane