Re: [SQL] i have table

[Achilleas Mantzios]

Στις Τετάρτη 04 Οκτώβριος 2006 18:37, ο/η Erik Jones έγραψε:
> Aaron Bono wrote:
> > On 10/4/06, *Erik Jones* <[EMAIL PROTECTED] >
> > wrote:
> >
> > There is one non-SQL related reason that I like to be able to order
> > columns, at least the way they are displayed whenever the table is
> > described:  human comprehension.  For example, I like to group all
> > keys
> > in a table before data, that includes primary as well as foreign
> > keys.
> > So, say I'm building on to an existing application and I need to do
> > an ALTER TABLE on an existing table to add a foreign key to an existing
> > table.  I'd like that key to be listed with the other keys, but presently
> > that's not possible in a simple way and, to be honest, I usually just go
> > without as the process you've described below is too prone to user
> > (human) error when dealing with live, sensitive data for me to want to
> > mess with it.
> >
> >
> > Ah, but it is possible... if you use views.
> >
> > I recommend you build views and query off them.  Then you can control
> > the order the columns will appear.
>
> Which would be great if I didn't have (many) thousands of lines of code
> that already use the tables.  Besides, this is no where near a 'make or
> break' thing.  It's just a matter of aesthetic preference.

Alright, you could play with something like:

UPDATE pg_attribute SET attnum =  where 
attrelid= and attname='';

but do some research of possible bad side effects.
-- 
Achilleas Mantzios

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] i have table

[Tom Lane]

Achilleas Mantzios <[EMAIL PROTECTED]> writes:
> Alright, you could play with something like:

> UPDATE pg_attribute SET attnum =  where 
> attrelid= and attname='';

That's guaranteed to break his table, because the physical storage
of the rows won't have changed.  (To name only the most obvious
problem...)

regards, tom lane

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [SQL] i have table

[Achilleas Mantzios]

Στις Πέμπτη 05 Οκτώβριος 2006 16:31, ο/η Tom Lane έγραψε:
> Achilleas Mantzios <[EMAIL PROTECTED]> writes:
> > Alright, you could play with something like:
> >
> > UPDATE pg_attribute SET attnum =  where
> > attrelid= and attname='';
>
> That's guaranteed to break his table, because the physical storage
> of the rows won't have changed.  (To name only the most obvious
> problem...)

Ooops
Thanx for the info, altho i warned the guy to search before act.

>
>   regards, tom lane
>
> ---(end of broadcast)---
> TIP 5: don't forget to increase your free space map settings

-- 
Achilleas Mantzios

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [SQL] i have table

[Andrew Sullivan]

On Wed, Oct 04, 2006 at 11:20:07AM -0500, Aaron Bono wrote:
> 
> So do it as needed and convert your application slowly.

You don't even need to do that.  

ALTER TABLE tablename RENAME TO tablename_real;

CREATE VIEW tablename [&c.]

Now the view looks to the application just like the old table.  If
you want to insert &c., you put some rules there.

A

-- 
Andrew Sullivan  | [EMAIL PROTECTED]
Users never remark, "Wow, this software may be buggy and hard 
to use, but at least there is a lot of code underneath."
--Damien Katz

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

timestamp subtraction (was Re: [SQL] formatting intervals with to_char)

[Tom Lane]

Graham Davis <[EMAIL PROTECTED]> writes:
> The documentation for to_char states that:

> "|to_char(interval)| formats HH and HH12 as hours in a single day, while 
> HH24 can output hours exceeding a single day, e.g. >24."

> However I can not get it to work with time intervals that span more than 
> 1 day.

Well, it does in fact print intervals exceeding 24 hours:

regression=# select to_char('48 hours'::interval, 'HH24:MI:SS');
 to_char
--
 48:00:00
(1 row)

However, '48 hours' and '2 days' are not the same thing.  The problem
with the case you give is really that timestamp_mi applies justify_hours
to its result --- that is, 

regression=# select ('2006-09-15 23:59:00'::timestamp - '2006-09-01 
09:30:41'::timestamp);
 ?column?
--
 14 days 14:28:19
(1 row)

should be reporting '350:28:19' instead.

This is a hack that was done to minimize the changes in the regression
test expected outputs when we changed type interval from months/seconds
to months/days/seconds.  But I wonder whether it wasn't a dumb idea.
It is certainly inconsistent, as noted in the code comments.

I'm tempted to propose that we remove the justify_hours call, and tell
anyone who really wants the old results to apply justify_hours() to the
subtraction result for themselves.  Not sure what the fallout would be,
though.

regards, tom lane

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

[SQL] age() vs. timestamp substraction

[Martin Marques]

I just found this problem with the age() function, which AFAIK should give the 
same resulte as a subtraction of the argument from now(), but it doesn't.


prueba=> SELECT (now() - tc.last_cron),age(tc.last_cron),tc.intervalo FROM 
tareas_cron tc ;
  ?column?   |  age  | intervalo
-+---+---
 @ 1 day 15 hours 13 mins 12.06 secs | @ 23 hours 59 mins 58.47 secs | @ 1 day
 @ 15 hours 13 mins 12.06 secs   | @ 1.52 secs ago   | @ 30 mins
 @ 15 hours 13 mins 12.08 secs   | @ 1.50 secs ago   | @ 10 mins

prueba=> SELECT version();
version

 PostgreSQL 8.1.3 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.0.3 (Debian 
4.0.3-1)

Any ideas on why?

I starting to change my queries so they don't have the age() function anymore.

--
-
Lic. Martín Marqués |   SELECT 'mmarques' || 
Centro de Telemática|   '@' || 'unl.edu.ar';
Universidad Nacional|   DBA, Programador, 
del Litoral |   Administrador
-



---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] Assigning a timestamp without timezone to a timestamp

[Andrew Sullivan]

On Wed, Oct 04, 2006 at 11:04:56AM -0700, Hector Villarreal wrote:
> Hi 
> I am also interested in this type of setup. However, in the example
> below
> I am a little confused as to why the table entry is 1, -3 

The 1 is an artificial key (it's the criterion in the WHERE clause). 
The -03 is the time zone offset.  The most reliable way to handle
time zone offsets, I find, is to use the numeric offset from UTC. 
That's the way PostgreSQL shows them in some cases, too.  On my
system, for instance, I get this for SELECT now() (at the moment):

  now  
---
 2006-10-05 14:21:51.507419-04
(1 row)

> SELECT a.timestamp::timestamptz from (SELECT '2006-10-03
> 09:00'||"timezone" as timestamp from storetz where id = 1) as a;
>timestamp

So what this does is 

SELECT

the column named "timestamp" from relation "a"
cast to timestamp with time zone (the :: is a shorthand for
cast in Postgres)

FROM 

a relation called "a" 
constituted as (this is that "as a" on the end)

SELECT 
the literal string '2006-10-03 09:00'
concatenated to (that's what "||" means)
the column "timezone"
[and call that whole thing "timestamp"
FROM
a relation called "storetz"
WHERE
the storetz row has an id of 1.

So, what you get is a timestamp with a time zone that is built up
from the combination of a timestamp without time zone and some time
zone data that you have.

What's _really_ cool in Postgres about the time handling is that you
can also change your time zone, and find that the data nicely
represents your new time zone too.  You can see this in my original
example: I was using GMT, but inserted a timestamp in -03.  When I
selected the answer, though, I got one back in GMT (==UTC).  So
that's why you see this:

> 
> testing=# SELECT a.timestamp::timestamptz from (SELECT '2006-10-03
> 09:00'||"timezone" as timestamp from storetz where id = 1) as a;
>timestamp
> 
>  2006-10-03 12:00:00+00
> (1 row)

2006-10-03 09:00:00-03 == 2006-10-03 12:00:00+00

Hope that helps,
A

-- 
Andrew Sullivan  | [EMAIL PROTECTED]
The fact that technology doesn't work is no bar to success in the marketplace.
--Philip Greenspun

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [SQL] age() vs. timestamp substraction

[Tom Lane]

Martin Marques  writes:
> I just found this problem with the age() function, which AFAIK should
> give the same resulte as a subtraction of the argument from now(),

Where did you get that idea?  age's reference point is current_date (ie,
midnight) not now().  There are also some differences in the calculation
compared to a plain timestamp subtraction.

regards, tom lane

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [SQL] age() vs. timestamp substraction

[Martin Marques]

On Thu, 05 Oct 2006 14:37:24 -0400, Tom Lane <[EMAIL PROTECTED]> wrote:
> Martin Marques  writes:
>> I just found this problem with the age() function, which AFAIK should
>> give the same resulte as a subtraction of the argument from now(),
> 
> Where did you get that idea?  age's reference point is current_date (ie,
> midnight) not now().  There are also some differences in the calculation
> compared to a plain timestamp subtraction.

Ignore anything I said. Just realized it said current_date. :-(

Sorry.

--
-
Lic. Martín Marqués |   SELECT 'mmarques' || 
Centro de Telemática|   '@' || 'unl.edu.ar';
Universidad Nacional|   DBA, Programador, 
del Litoral |   Administrador
-



---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[SQL] On Rollback my sequency does not back the initial value

[Ezequias Rodrigues da Rocha]

Hi,I am implementing a function that execute a rollback then some exception is throwed.The problem is that my nextval('mysequency') does not return to the initial value when the rollback is executed.
Does anybody have any experiency with it ?Regards ...--  =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-  Atenciosamente (Sincerely)
Ezequias Rodrigues da Rocha =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-A pior das democracias ainda é melhor do que a melhor das ditadurasThe worst of democracies is still better than the better of dictatorships
 http://ezequiasrocha.blogspot.com/

[SQL] Postgresql quey planner

[William Leite Araújo]

  Estou confuso com o funcionamento do "query planner" do postgresql. Tenho 2 queries conceitualmente idênticas:  I'm confused about the planner functionality. I'd 2 queries contextualy indentical:
SELECT p.pos_id, count(aut_id) as pesadosFROM posto p LEFT OUTER JOIN 
pesagem e USING(pos_id)WHERE e.pos_id IS NULL OR (pes_dat_tstam 
BETWEEN '2006-03-01' AND '2006-03-31')GROUP BY
 p.pos_id ORDER BY pos_idSELECT p.pos_id, count(pes_id) as autuados
FROM posto p LEFT OUTER JOIN autuacao a USING(pos_id)WHERE a.pos_id 
IS NULL OR (aut_dat_tstam BETWEEN '2006-03-01' AND '2006-03-31'
)GROUP BY p.pos_id ORDER BY pos_id A segunda faz realmente o que eu desejo. Mostra todos os postos, inclusive os que não apresentam resultado. Já a primeira, alguns postos simplesmente não aparecem. A diferença entre as tabelas é o número de registros. A tabela de "pesagem" possui muito mais registros que a de "autuacao". Avaliando o planner, ví que realmente trada de forma muito diferente as consultas, mas não entendi porque o resultado e afetado. Não deveria. A saida do planner é :
 The second does really what I desire. Show all records in table "posto", including that's without count in table "autuados". But the first, doesn't. Some elements on table "Posto" are ommited. Seeing the planner output, I couldn't understand why he uses "GroupAggregate" on the first but not on the second query. The relevant difference on tables is the number of records. "pesagem" has about 22000 but "autuacao" only 100. Is my queries wrongs? 
'GroupAggregate  (cost=0.00..1027.66 rows=10 width=8) (actual time=0.318..30.741 rows=6 loops=1)''  ->  Merge Left Join  (cost=0.00..1027.43 rows=21 width=8) (actual time=0.289..30.651 rows=54 loops=1)'
'    Merge Cond: ("outer".pos_id = "inner".pos_id)''    Filter: (("inner".pos_id IS NULL) OR (("inner".pes_dat_tstam >= '2006-03-01'::date) AND ("inner".pes_dat_tstam <= '2006-03-31'::date)))'
'    ->  Index Scan using prk_posto on posto p  (cost=0.00..5.35 rows=10 width=4) (actual time=0.064..0.081 rows=10 loops=1)''    ->  Index Scan using fki_frk_pos_id on pesagem a  (cost=0.00..801.48 rows=12604 width=12) (actual time=
0.059..16.331 rows=12604 loops=1)''Total runtime: 31.035 ms''Sort  (cost=8.38..8.41 rows=10 width=8) (actual time=0.557..0.564 rows=10 loops=1)''  Sort Key: p.pos_id''  ->  HashAggregate  (cost=
8.09..8.22 rows=10 width=8) (actual time=0.520..0.532 rows=10 loops=1)''    ->  Merge Right Join  (cost=1.27..7.85 rows=49 width=8) (actual time=0.062..0.443 rows=58 loops=1)''  Merge Cond: ("outer".pos_id = "inner".pos_id)'
'  Filter: (("outer".pos_id IS NULL) OR (("outer".aut_dat_tstam >= '2006-03-01'::date) AND ("outer".aut_dat_tstam <= '2006-03-31'::date)))''  ->  Index Scan using idx_autuacao_pos_id on autuacao a  (cost=
0.00..4.85 rows=77 width=12) (actual time=0.007..0.103 rows=77 loops=1)''  ->  Sort  (cost=1.27..1.29 rows=10 width=4) (actual time=0.049..0.107 rows=86 loops=1)''    Sort Key: p.pos_id
''    ->  Seq Scan on posto p  (cost=0.00..1.10 rows=10 width=4) (actual time=0.005..0.016 rows=10 loops=1)''Total runtime: 0.659 ms'  Não consigo entender porquê o primeiro usa no final "GroupAggregate" mas o segundo não. É erro?
  Atenciosamente,-- William Leite Araújo

Re: [SQL] On Rollback my sequency does not back the initial value

[A. Kretschmer]

am  Thu, dem 05.10.2006, um 15:50:58 -0300 mailte Ezequias Rodrigues da Rocha 
folgendes:
> Hi,
> 
> I am implementing a function that execute a rollback then some exception is
> throwed.
> 
> The problem is that my nextval('mysequency') does not return to the initial
> value when the rollback is executed.

Right, a sequence doesn't rollback.

-- cite:
Advance the sequence object to its next value and return that value.
This is done atomically: even if multiple sessions execute nextval
concurrently, each will safely receive a distinct sequence value.
--

Source:
http://www.postgresql.org/docs/8.1/interactive/functions-sequence.html


HTH, Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47215,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] Postgresql quey planner

[Tom Lane]

"=?ISO-8859-1?Q?William_Leite_Ara=FAjo?=" <[EMAIL PROTECTED]> writes:
>  The second does really what I desire. Show all records in table
> "posto", including that's without count in table "autuados". But the first,
> doesn't. Some elements on table "Posto" are ommited.

What PG version is this?  8.1.0 through 8.1.3 had a logic bug in merge
right joins, which perhaps is your problem, although the commit message
only mentions duplicate rows not missing ones.

2006-03-17 14:38  tgl

* src/: backend/executor/nodeMergejoin.c,
test/regress/expected/join.out, test/regress/expected/join_1.out,
test/regress/sql/join.sql (REL8_1_STABLE): Fix bug introduced into
mergejoin logic by performance improvement patch of 2005-05-13. 
When we find that a new inner tuple can't possibly match any outer
tuple (because it contains a NULL), we can't immediately skip the
tuple when we are in NEXTINNER state.  Doing so can lead to
emitting multiple copies of the tuple in FillInner mode, because we
may rescan the tuple after returning to a previous marked tuple. 
Instead, proceed to NEXTOUTER state the same as we used to do. 
After we've found that there's no need to return to the marked
position, we can go to SKIPINNER_ADVANCE state instead of SKIP_TEST
when the inner tuple is unmatchable; this preserves the performance
improvement.  Per bug report from Bruce.  I also made a couple of
cosmetic code rearrangements and added a regression test for the
problem.

regards, tom lane

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [SQL] Assigning a timestamp without timezone to a timestamp

[chrisj]

Hi Hector,

It would probably better to get the explanation from Andrew, but I will do
the best I can.

You asked about the 1 and -3.  The 1 would be the store number in my
original scenario and -3 would be the representation of the timezone (three
hours behind Universal Coordinate Time).

I still have not had a chance to implement the solution into my application,
but I am assuming the -3 could also be a mnemonic such as "EDT"  I live in
Toronto EDT is Eastern Daylight-savings Time.

As for the syntax of the select, it is simply casting a character
representation of a timestamp concatenated with a character representation
of timezone to timestamptz.

In hindsight it is so simple I can't believe I could not come up with it
myself. 


Hector Villarreal wrote:
> 
> Hi 
> I am also interested in this type of setup. However, in the example
> below
> I am a little confused as to why the table entry is 1, -3 
> And the subsequent select statement . I would appreciate an explanation
> on the select statement. I do not understand the syntax. 
> Thanks in advance 
> Hector Villarreal
> SELECT a.timestamp::timestamptz from (SELECT '2006-10-03
> 09:00'||"timezone" as timestamp from storetz where id = 1) as a;
>timestamp
> 
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Andrew Sullivan
> Sent: Tuesday, October 03, 2006 7:52 AM
> To: chrisj
> Cc: pgsql-sql@postgresql.org
> Subject: Re: [SQL] Assigning a timestamp without timezone to a timestamp
> 
> On Tue, Oct 03, 2006 at 07:26:52AM -0700, chrisj wrote:
>> location, but they are the directive to all store locations saying:
> "In the
>> context of the timezone your store is located in,  these are the hours
> you
>> should be open.
> 
> Ah.  Well, then, right, it _does_ have to be timezone free.  That's
> actually the only case I'd use that.  Sorry, I'm dim, and didn't
> understand properly what you were doing.  (I read the "relative to
> the store's own time zone" to refer to the corporate office.  No, I
> don't know why, either.  Told you I'm dim.)
> 
> Anyway, here's something that worked for me (expanding this into your
> case ought not to be too tricky):
> 
> testing=# SELECT * from storetz ;
>  id | timezone 
> +--
>   1 | -03
> (1 row)
> 
> testing=# SELECT a.timestamp::timestamptz from (SELECT '2006-10-03
> 09:00'||"timezone" as timestamp from storetz where id = 1) as a;
>timestamp
> 
>  2006-10-03 12:00:00+00
> (1 row)
> 
> A
> -- 
> Andrew Sullivan  | [EMAIL PROTECTED]
> When my information changes, I alter my conclusions.  What do you do
> sir?
>   --attr. John Maynard Keynes
> 
> ---(end of broadcast)---
> TIP 5: don't forget to increase your free space map settings
> 
> ---(end of broadcast)---
> TIP 6: explain analyze is your friend
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Assigning-a-timestamp-without-timezone-to-a-timestamp-with-timezone-tf2373845.html#a6667349
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.


---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [SQL] Assigning a timestamp without timezone to a timestamp

[chrisj]

Hi Andrew,

If only all time zones were fixed offset timezones life would be so much
simpler.

Unfortunately the main area of deployment of my app will beToronto which is
on EDT which is not a fixed offsets timezone.  I hope/assume your solution
works with "EDT" instead of "-3", I will test it soon.


Andrew Sullivan wrote:
> 
> On Wed, Oct 04, 2006 at 11:04:56AM -0700, Hector Villarreal wrote:
>> Hi 
>> I am also interested in this type of setup. However, in the example
>> below
>> I am a little confused as to why the table entry is 1, -3 
> 
> The 1 is an artificial key (it's the criterion in the WHERE clause). 
> The -03 is the time zone offset.  The most reliable way to handle
> time zone offsets, I find, is to use the numeric offset from UTC. 
> That's the way PostgreSQL shows them in some cases, too.  On my
> system, for instance, I get this for SELECT now() (at the moment):
> 
>   now  
> ---
>  2006-10-05 14:21:51.507419-04
> (1 row)
> 
>> SELECT a.timestamp::timestamptz from (SELECT '2006-10-03
>> 09:00'||"timezone" as timestamp from storetz where id = 1) as a;
>>timestamp
> 
> So what this does is 
> 
> SELECT
> 
> the column named "timestamp" from relation "a"
>   cast to timestamp with time zone (the :: is a shorthand for
>   cast in Postgres)
> 
> FROM 
> 
> a relation called "a" 
>   constituted as (this is that "as a" on the end)
> 
>   SELECT 
>   the literal string '2006-10-03 09:00'
>   concatenated to (that's what "||" means)
>   the column "timezone"
>   [and call that whole thing "timestamp"
>   FROM
>   a relation called "storetz"
>   WHERE
>   the storetz row has an id of 1.
> 
> So, what you get is a timestamp with a time zone that is built up
> from the combination of a timestamp without time zone and some time
> zone data that you have.
> 
> What's _really_ cool in Postgres about the time handling is that you
> can also change your time zone, and find that the data nicely
> represents your new time zone too.  You can see this in my original
> example: I was using GMT, but inserted a timestamp in -03.  When I
> selected the answer, though, I got one back in GMT (==UTC).  So
> that's why you see this:
> 
>> 
>> testing=# SELECT a.timestamp::timestamptz from (SELECT '2006-10-03
>> 09:00'||"timezone" as timestamp from storetz where id = 1) as a;
>>timestamp
>> 
>>  2006-10-03 12:00:00+00
>> (1 row)
> 
> 2006-10-03 09:00:00-03 == 2006-10-03 12:00:00+00
> 
> Hope that helps,
> A
> 
> -- 
> Andrew Sullivan  | [EMAIL PROTECTED]
> The fact that technology doesn't work is no bar to success in the
> marketplace.
>   --Philip Greenspun
> 
> ---(end of broadcast)---
> TIP 5: don't forget to increase your free space map settings
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Assigning-a-timestamp-without-timezone-to-a-timestamp-with-timezone-tf2373845.html#a6667446
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [SQL] Assigning a timestamp without timezone to a timestamp

[Andrew Sullivan]

On Thu, Oct 05, 2006 at 01:06:00PM -0700, chrisj wrote:
> If only all time zones were fixed offset timezones life would be so much
> simpler.

Indeed.

> Unfortunately the main area of deployment of my app will beToronto which is
> on EDT which is not a fixed offsets timezone.  I hope/assume your solution
> works with "EDT" instead of "-3", I will test it soon.

Should do, although you'll need more than EDT.  EDT is also fixed:
it's UTC-4.  EST5EDT isn't, though, so you could use that (you'd need
to improve your schema, though, because you had char(3) there, and
not all time zones are 3 characters long).  But to answer your
question, yes, it works.  I just tried it.

A


-- 
Andrew Sullivan  | [EMAIL PROTECTED]
Information security isn't a technological problem.  It's an economics
problem.
--Bruce Schneier

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [SQL] Assigning a timestamp without timezone to a timestamp

[chrisj]

Thanks for the heads up, I definately need  EST5EDT

you saved me twice!!


Andrew Sullivan wrote:
> 
> On Thu, Oct 05, 2006 at 01:06:00PM -0700, chrisj wrote:
>> If only all time zones were fixed offset timezones life would be so much
>> simpler.
> 
> Indeed.
> 
>> Unfortunately the main area of deployment of my app will beToronto which
>> is
>> on EDT which is not a fixed offsets timezone.  I hope/assume your
>> solution
>> works with "EDT" instead of "-3", I will test it soon.
> 
> Should do, although you'll need more than EDT.  EDT is also fixed:
> it's UTC-4.  EST5EDT isn't, though, so you could use that (you'd need
> to improve your schema, though, because you had char(3) there, and
> not all time zones are 3 characters long).  But to answer your
> question, yes, it works.  I just tried it.
> 
> A
> 
> 
> -- 
> Andrew Sullivan  | [EMAIL PROTECTED]
> Information security isn't a technological problem.  It's an economics
> problem.
>   --Bruce Schneier
> 
> ---(end of broadcast)---
> TIP 1: if posting/reading through Usenet, please send an appropriate
>subscribe-nomail command to [EMAIL PROTECTED] so that your
>message can get through to the mailing list cleanly
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Assigning-a-timestamp-without-timezone-to-a-timestamp-with-timezone-tf2373845.html#a6668169
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] Assigning a timestamp without timezone to a timestamp

[Hector Villarreal]

Many thanks Chris,
   I am new to Postgresql and was trying to understand the casting
portion. Appreciate it as this makes it useful for many applications
where timezones matter. 

Thanks
Hector

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of chrisj
Sent: Thursday, October 05, 2006 1:02 PM
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] Assigning a timestamp without timezone to a timestamp


Hi Hector,

It would probably better to get the explanation from Andrew, but I will
do
the best I can.

You asked about the 1 and -3.  The 1 would be the store number in my
original scenario and -3 would be the representation of the timezone
(three
hours behind Universal Coordinate Time).

I still have not had a chance to implement the solution into my
application,
but I am assuming the -3 could also be a mnemonic such as "EDT"  I live
in
Toronto EDT is Eastern Daylight-savings Time.

As for the syntax of the select, it is simply casting a character
representation of a timestamp concatenated with a character
representation
of timezone to timestamptz.

In hindsight it is so simple I can't believe I could not come up with it
myself. 


Hector Villarreal wrote:
> 
> Hi 
> I am also interested in this type of setup. However, in the example
> below
> I am a little confused as to why the table entry is 1, -3 
> And the subsequent select statement . I would appreciate an
explanation
> on the select statement. I do not understand the syntax. 
> Thanks in advance 
> Hector Villarreal
> SELECT a.timestamp::timestamptz from (SELECT '2006-10-03
> 09:00'||"timezone" as timestamp from storetz where id = 1) as a;
>timestamp
> 
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Andrew Sullivan
> Sent: Tuesday, October 03, 2006 7:52 AM
> To: chrisj
> Cc: pgsql-sql@postgresql.org
> Subject: Re: [SQL] Assigning a timestamp without timezone to a
timestamp
> 
> On Tue, Oct 03, 2006 at 07:26:52AM -0700, chrisj wrote:
>> location, but they are the directive to all store locations saying:
> "In the
>> context of the timezone your store is located in,  these are the
hours
> you
>> should be open.
> 
> Ah.  Well, then, right, it _does_ have to be timezone free.  That's
> actually the only case I'd use that.  Sorry, I'm dim, and didn't
> understand properly what you were doing.  (I read the "relative to
> the store's own time zone" to refer to the corporate office.  No, I
> don't know why, either.  Told you I'm dim.)
> 
> Anyway, here's something that worked for me (expanding this into your
> case ought not to be too tricky):
> 
> testing=# SELECT * from storetz ;
>  id | timezone 
> +--
>   1 | -03
> (1 row)
> 
> testing=# SELECT a.timestamp::timestamptz from (SELECT '2006-10-03
> 09:00'||"timezone" as timestamp from storetz where id = 1) as a;
>timestamp
> 
>  2006-10-03 12:00:00+00
> (1 row)
> 
> A
> -- 
> Andrew Sullivan  | [EMAIL PROTECTED]
> When my information changes, I alter my conclusions.  What do you do
> sir?
>   --attr. John Maynard Keynes
> 
> ---(end of
broadcast)---
> TIP 5: don't forget to increase your free space map settings
> 
> ---(end of
broadcast)---
> TIP 6: explain analyze is your friend
> 
> 

-- 
View this message in context:
http://www.nabble.com/Assigning-a-timestamp-without-timezone-to-a-timest
amp-with-timezone-tf2373845.html#a6667349
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.


---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [HACKERS] timestamp subtraction (was Re: [SQL] formatting intervals with to_char)

[Michael Glaesemann]

On Oct 6, 2006, at 1:50 , Tom Lane wrote:


I'm tempted to propose that we remove the justify_hours call, and tell
anyone who really wants the old results to apply justify_hours() to  
the
subtraction result for themselves.  Not sure what the fallout would  
be,

though.


I'm tempted to support such a proposal. Is this something that we'd  
want to do for 8.2? There are some interval range checking fixes I'm  
working on for 8.3. Perhaps this could be rolled into that as well?  
Then again, range checking and behavior are two separate things.  
Considering how late it is in the cycle, perhaps the change in  
behavior should come in 8.3.


Michael Glaesemann
grzm seespotcode net



---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [HACKERS] timestamp subtraction (was Re: [SQL] formatting intervals with to_char)

[Tom Lane]

Michael Glaesemann <[EMAIL PROTECTED]> writes:
> Considering how late it is in the cycle, perhaps the change in  
> behavior should come in 8.3.

Yeah, there's not really enough time to think through the consequences
now.  I'd like to experiment with it for 8.3 though.

regards, tom lane

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [HACKERS] timestamp subtraction (was Re: [SQL] formatting intervals with to_char)

[Jim Nasby]

On Oct 5, 2006, at 11:50 AM, Tom Lane wrote:
regression=# select ('2006-09-15 23:59:00'::timestamp - '2006-09-01  
09:30:41'::timestamp);

 ?column?
--
 14 days 14:28:19
(1 row)

should be reporting '350:28:19' instead.

This is a hack that was done to minimize the changes in the regression
test expected outputs when we changed type interval from months/ 
seconds

to months/days/seconds.  But I wonder whether it wasn't a dumb idea.
It is certainly inconsistent, as noted in the code comments.

I'm tempted to propose that we remove the justify_hours call, and tell
anyone who really wants the old results to apply justify_hours() to  
the
subtraction result for themselves.  Not sure what the fallout would  
be,

though.


I suspect there's applications out there that are relying on that  
being nicely formated for display purposes.


I agree it should be removed, but we might need a form of backwards  
compatibility for a version or two...

--
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB  http://enterprisedb.com  512.569.9461 (cell)



---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq