Re: [SQL] convert in GMT time zone without summer time

2011-04-16 Thread Jasen Betts 
On 2011-04-15, LaraK  wrote:
> Hello,
>
> I want write a function that converts a timestamp with time zone to the UTC
> zone. But it should all be stored in the winter time.
>
> For example, it must now, in the summer, the German time back by 2 hours and
> in the winter time only 1 hour. But it expects only back one hour.
>
> Is there a function or a specific time zone?

if I undestand your goal correctly you want to subtract the daylight
savings offset from the given timezone if daylight-savings is in use
in the current time locale.

you can detect daylight-savings by setting testing the timezone offset
at 3 month intervals ( timestamp, timestamp+3months timestamp-3months,
timestamp+6months, timestamp-6months)

the one(s) of them with the least (most negative) offset from UTC will
represent non daylight-saving time.

if your given time has a different offset it's daylight saving time,
add the difference.

 calling:
> [CODE]
> SELECT
> to_char(CONVERT_TO_UTC(to_timestamp('2011-03-22 14:17:00', '-MM-DD
> hh24:MI:SS'), 'CET'), '-mm-dd hh24:MI:SS') AS winter,
> to_char(CONVERT_TO_UTC(to_timestamp('2011-04-22 14:17:00', '-MM-DD
> hh24:MI:SS'), 'CET'), '-mm-dd hh24:MI:SS') AS summer
> [/CODE]
>
> must come out:
> [CODE]
> WINTER| SUMMER
> +-
> 2011-03-22 13:17:00 | 2011-04-22 12:17:00
> [/CODE]

that test case is ambiguous your inputs are timespamptz but 
have an unspecified timezone (and so get the zone appropriate to 
your time locale). I'm assuming your time locale is "Europe/Berlin" 
and you really mean the following:

SELECT to_char(CONVERT_TO_UTC( '2011-03-22 14:17:00+01'::timestamptz
,'CET'),'-mm-dd hh24:MI:SS') AS winter, to_char(CONVERT_TO_UTC(
'2011-04-22 14:17:00+02'::timestamptz ,'CET'),'-mm-dd hh24:MI:SS')
AS summer;

CREATE OR REPLACE FUNCTION 
  CONVERT_TO_UTC ( timestamptz,  text) returns timestamp as 
  $$ SELECT $1 at time zone 'UTC'; $$ 
  language sql;
 
In that this function does not use the second parameter it may not be
what you want, on the other hand it's function matches it's name well.
what are you trying to do?

-- 
⚂⚃ 100% natural


-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Get id of a tuple using exception

2011-04-16 Thread Jasen Betts 
On 2011-04-14, f vf  wrote:
> --000e0cd2bf6a60c30804a0dec84b
> Content-Type: text/plain; charset=ISO-8859-1
>
> Hello,
> i'm using a pl/sql procedure and I prevent inserting duplicate tuples using
> an exception for example:
>
>   BEGIN
>INSERT INTO "Triples"(id, subject, predicate, "object")
> VALUES (id, sub_i, pred_i, obj_i);
> * EXCEPTION WHEN unique_violation THEN
> --do something.
>
> *In some cases I have interest in getting the id of the tuple that was
> already in the table when the exception is triggered. Is there a way for the
> EXCEPTION to return that id instead of using a select to know wich was the
> id of the triple already existing in the table?

if the unique violation is on the ID column that's easy, if it's on
some other constraint then no there's no way to get the id.

do a select first looking for the colliding row

then fall back to an insert.

there may be weaknesses with this, it depends on why you need the Id.


-- 
⚂⚃ 100% natural


-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql