Re: [SQL] Problem with intervals
On Tuesday 02 December 2003 05:09, Bob Smith wrote:
> I'm getting an unexpected result using intervals in an expression:
>
> select ('2003-10-26 0:00:00'::timestamp + '1 day'::interval)::date;
> date
>
> 2003-10-26
> I assumed '1 day' would always increment the date by 1, but it appears
> that '1 day' just means '24 hours', and due to the daylight/standard
> time shift, October 26 was 25 hours long this year.
>
> Is this a Postgres bug, or is this correct SQL behavior? I'm running
> Postgres 7.2.2.
Expected, because you're acting on a timestamp. When you start looking at time
handling across timezones and daylight saving systems across the world it
does get a bit complicated.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 3: 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] Validity check in to_date?
>
> I just discovered that to_date() function does not check if supplied
> date is correct, giving surprising (at least for me) results:
>
> fduch=# SELECT to_date('31.11.2003', 'DD.MM.');
> to_date
>
> 2003-12-01
>
> or even
>
> fduch=# SELECT to_date('123.45.2003', 'DD.MM.');
> to_date
>
> 2007-01-03
>
> to_timestamp() seems to work the same way. It's probably useful sometimes,
> but not in my case... Is it how it supposed to work?
> If so, how can I do such a validity check?
> If not, has something changed in 7.4?
>
> In any case, I have to find a workaround now and will appreciate any help.
>
>
> fduch=# SELECT version();
>version
> -
> PostgreSQL 7.3.4 on i386-portbld-freebsd4.8, compiled by GCC 2.95.4
>
>
> --
> Fduch M. Pravking
>
As far as I know these results are correct in terms of the underlying
C-library function mktime(). This function is intended to be used when
adding/subtracting intervals from a given timestamp.
I don't know of any postgres function doing the check you're looking for.
But I can't believe this is the first time this topic is brought up.
You may search the archives on "date plausibility" are related terms.
HTH
Regards, Christoph
---(end of broadcast)---
TIP 3: 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] Validity check in to_date?
On Tue, Dec 02, 2003 at 10:27:04AM +0100, Christoph Haller wrote:
> >
> > I just discovered that to_date() function does not check if supplied
> > date is correct, giving surprising (at least for me) results:
> >
> > fduch=# SELECT to_date('31.11.2003', 'DD.MM.');
> > to_date
> >
> > 2003-12-01
> >
> > or even
> >
> > fduch=# SELECT to_date('123.45.2003', 'DD.MM.');
> > to_date
> >
> > 2007-01-03
> >
> > to_timestamp() seems to work the same way. It's probably useful sometimes,
> > but not in my case... Is it how it supposed to work?
> > If so, how can I do such a validity check?
> > If not, has something changed in 7.4?
No change in 7.4. Maybe in 7.5 or in some 7.4.x.
> As far as I know these results are correct in terms of the underlying
> C-library function mktime(). This function is intended to be used when
> adding/subtracting intervals from a given timestamp.
> I don't know of any postgres function doing the check you're looking for.
> But I can't believe this is the first time this topic is brought up.
> You may search the archives on "date plausibility" are related terms.
The others PostgreSQL stuff which full parse (means check ranges)
date/time is less optimistic with this:
# select '31.11.2003'::date;
ERROR: date/time field value out of range: "31.11.2003"
Karel
--
Karel Zak <[EMAIL PROTECTED]>
http://home.zf.jcu.cz/~zakkr/
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Validity check in to_date?
On Tue, Dec 02, 2003 at 10:27:04AM +0100, Christoph Haller wrote: > As far as I know these results are correct in terms of the underlying > C-library function mktime(). This function is intended to be used when > adding/subtracting intervals from a given timestamp. Which one? mktime() or to_date()? I'm not sure it's handy to use to_date() for any calculations, so I'm surprised why doesn't it work just as date_in() do. > I don't know of any postgres function doing the check you're looking for. Yes, the only thing I could think now is to do something like s/([0-9]+)\.([0-9]+)\.([0-9]+)/\3-\2-\1/ and then pass it to CAST(... AS date) using ISO DateStyle. (I could simply use German DateStyle in case of DD.MM., but I deal with several date formats, e.g. DD/MM/.) > But I can't believe this is the first time this topic is brought up. > You may search the archives on "date plausibility" are related terms. I'm sure too, but it's really hard to find a good keyword sequence when searching such sort of things :( Anyway, thank you for attention. -- Fduch M. Pravking ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Validity check in to_date?
On Tue, Dec 02, 2003 at 10:57:14AM +0100, Karel Zak wrote: > > > If not, has something changed in 7.4? > > No change in 7.4. Maybe in 7.5 or in some 7.4.x. Well, let's see. > The others PostgreSQL stuff which full parse (means check ranges) > date/time is less optimistic with this: > > # select '31.11.2003'::date; > ERROR: date/time field value out of range: "31.11.2003" Exactly! But date_in formats are too limited and "floaty", especially in 7.3 or less. -- Fduch M. Pravking ---(end of broadcast)--- TIP 3: 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] Validity check in to_date?
T've been following this thread with interest because I have a related
problem. Basically we are storing dates in CHAR fields with al the
associated problems. I'd like to do it, but changing everything to date
fields isn't practical for now, so as a stopgap solution, I want to provide
some validation at the database level.
I tried:
create domain ymdtest2 as char(10) constraint valid_date check
(VALUE::DATE);
But it gives this error:
ERROR: cannot cast type character to date
I also tried:
create domain test char(10) check (CAST(VALUE AS DATE));
and it gives the same error. I don't need to actually modify VALUE, but I'd
like to have it so that any attempt to insert an invalid date will cause the
transaction to fail. This is OK:
SELECT CAST('2003-3-31' AS DATE);
So it is possible to cast type character to date (as we all know) so it
seems that the problem only applies to checks.
Now, I am wondering if there is a clever work around to this? It escapes me
for now anyway.
Regards
Iain
- Original Message -
From: "Karel Zak" <[EMAIL PROTECTED]>
To: "Christoph Haller" <[EMAIL PROTECTED]>
Cc: "Alexander M. Pravking" <[EMAIL PROTECTED]>;
<[EMAIL PROTECTED]>
Sent: Tuesday, December 02, 2003 6:57 PM
Subject: Re: [SQL] Validity check in to_date?
> On Tue, Dec 02, 2003 at 10:27:04AM +0100, Christoph Haller wrote:
> > >
> > > I just discovered that to_date() function does not check if supplied
> > > date is correct, giving surprising (at least for me) results:
> > >
> > > fduch=# SELECT to_date('31.11.2003', 'DD.MM.');
> > > to_date
> > >
> > > 2003-12-01
> > >
> > > or even
> > >
> > > fduch=# SELECT to_date('123.45.2003', 'DD.MM.');
> > > to_date
> > >
> > > 2007-01-03
> > >
> > > to_timestamp() seems to work the same way. It's probably useful
sometimes,
> > > but not in my case... Is it how it supposed to work?
> > > If so, how can I do such a validity check?
> > > If not, has something changed in 7.4?
>
> No change in 7.4. Maybe in 7.5 or in some 7.4.x.
>
> > As far as I know these results are correct in terms of the underlying
> > C-library function mktime(). This function is intended to be used when
> > adding/subtracting intervals from a given timestamp.
> > I don't know of any postgres function doing the check you're looking
for.
> > But I can't believe this is the first time this topic is brought up.
> > You may search the archives on "date plausibility" are related terms.
>
> The others PostgreSQL stuff which full parse (means check ranges)
> date/time is less optimistic with this:
>
> # select '31.11.2003'::date;
> ERROR: date/time field value out of range: "31.11.2003"
>
> Karel
>
> --
> Karel Zak <[EMAIL PROTECTED]>
> http://home.zf.jcu.cz/~zakkr/
>
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Validity check in to_date?
On Tue, Dec 02, 2003 at 07:44:54PM +0900, Iain wrote: > T've been following this thread with interest because I have a related > problem. Basically we are storing dates in CHAR fields with al the > associated problems. I'd like to do it, but changing everything to date > fields isn't practical for now, so as a stopgap solution, I want to provide > some validation at the database level. > > I tried: > > create domain ymdtest2 as char(10) constraint valid_date check > (VALUE::DATE); There's no conversion function from char(n) to date, but there's one from text to date. Try using check (VALUE::text::date). -- Fduch M. Pravking ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Validity check in to_date?
On Tue, Dec 02, 2003 at 01:55:06PM +0300, Alexander M. Pravking wrote: > Try using check (VALUE::text::date). Assuming check expects boolean result, it's (VALUE::text::date IS NOT NULL) -- Fduch M. Pravking ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Problem with intervals
On Mon, Dec 01, 2003 at 09:09:20PM -0800, Bob Smith wrote:
> I'm getting an unexpected result using intervals in an expression:
>
> select ('2003-10-26 0:00:00'::timestamp + '1 day'::interval)::date;
> date
>
> 2003-10-26
> (1 row)
Try using '2003-10-26 0:00:00'::date + 1;
integers do not lie ;-)
> When I get rid of the date cast it becomes clear what is happening:
>
> select '2003-10-26 0:00:00'::timestamp + '1 day'::interval;
> ?column?
>
> 2003-10-26 23:00:00-08
> (1 row)
>
> Is this a Postgres bug, or is this correct SQL behavior? I'm running
> Postgres 7.2.2.
It has been discussed several times, Tom Lane offered to add 'day' as
a separate interval unit (like 'second' and 'month' at this moment),
but noone took a shot at it, AFAIK.
Note also, that in 7.3 "timestamp" means "timestamp without time zone",
while in 7.2 it's "timestamp with time zone".
--
Fduch M. Pravking
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Problem with intervals
Bob Smith <[EMAIL PROTECTED]> writes: > '1 day' just means '24 hours' Yup. > Is this a Postgres bug, or is this correct SQL behavior? It's arguably a bug, but it's unfixable without a significant change in the internal representation and handling of intervals. I don't know when anyone will get annoyed enough to tackle it. In the meantime, consider using date +/- integer arithmetic instead of timestamp + interval. > I'm running Postgres 7.2.2. You really should be running something newer ... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Ooops ! :( Recovery techniques
> > My machine just crashed in the middle of an update. > > When I restart the postgresql server the database engine appears ok and the= > n tells me that a file /data/pg_clog/0697 is not there.=20 > > I presume that these are the transaction log files.=20 > I would like to truncate the old logfiles, re-index and keep going. > I that possible with postgres of do I have to do a full rebuild? > > thanks verymuch > > Richard Sydney-Smith > > Sorry, your presumption is wrong. In contrary to what the name might suggest this file is not a log file at all. Your dbase is probably seriously damaged. If you haven't made a dbase dump using "pg_dump" before, you'd better do that full rebuild you mentioned. Regards, Christoph PS I recommend to read the chapter 'Backup and Restore' of the fine manual to be prepared when your machine crashes again. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Validity check in to_date?
On Tue, 2 Dec 2003, Iain wrote: > T've been following this thread with interest because I have a related > problem. Basically we are storing dates in CHAR fields with al the > associated problems. I'd like to do it, but changing everything to date > fields isn't practical for now, so as a stopgap solution, I want to provide > some validation at the database level. > > I tried: > > create domain ymdtest2 as char(10) constraint valid_date check > (VALUE::DATE); > > But it gives this error: > > ERROR: cannot cast type character to date > > I also tried: > > create domain test char(10) check (CAST(VALUE AS DATE)); I'd try CAST(CAST(VALUE AS TEXT) AS DATE) There's a text->date conversion, but not one from character(n). ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Problem with intervals
On Tuesday, Dec 2, 2003, at 03:53 US/Pacific, Alexander M. Pravking
wrote:
On Mon, Dec 01, 2003 at 09:09:20PM -0800, Bob Smith wrote:
I'm getting an unexpected result using intervals in an expression:
select ('2003-10-26 0:00:00'::timestamp + '1 day'::interval)::date;
date
2003-10-26
(1 row)
Try using '2003-10-26 0:00:00'::date + 1;
integers do not lie ;-)
Aha! That solves my problem for now. I had also discovered that using
'25 hours'::interval works, but the integer approach is better.
It has been discussed several times, Tom Lane offered to add 'day' as
a separate interval unit (like 'second' and 'month' at this moment),
but noone took a shot at it, AFAIK.
Note also, that in 7.3 "timestamp" means "timestamp without time zone",
while in 7.2 it's "timestamp with time zone".
Yet another reason to upgrade, I guess I'm gonna have to do it soon...
Thanks for your help!
Bob S.
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Datatype Inet and Searching
Dear Users, I am very new to using the inet data type and I have a database with a list of CIDRs in it And I was hoping to be able to do some kind of select statement that would pull the record That the IP given is located in for example. If in my database I have a record like: Id cidr name 1 10.0.0.0/30 user1 2 10.0.0.4/30 user2 3 10.0.0.8/30 user3 I was hoping to be able to do something like: Select * from mytable where cidr <<=’10.0.0.5’ And the database would return only record 2 since it knows that 10.0.0.5 is contained only within record 2. I can’t seem to figure out the correct syntax to make this type of query happen. Any help would greatly Be appreciated. Devin Atencio
[SQL] [postgres] Text zu bytea casten
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hallo, ich habe in einer Tabelle eine Spalte mit dem Datentyp text, das ich nach bytea wandeln möchte (um später auch \000 speichern zu können). Direkt scheint ein Alter Table da nicht zu gehen, also lege ich eine temporäre Spalte an. Nur: > UPDATE forum_gtree SET gid2=gid::bytea; ERROR: Cannot cast type text to bytea Wie kann ich einen Text nach Bytea umwandeln? Umgekehrt scheint es mit encode/decode zu gehen ... http://www.postgresql.org/docs/current/interactive/functions-string.html Dank && Ciao Alvar - -- ** Alvar C.H. Freude -- http://alvar.a-blast.org/ ** Berufsverbot? http://odem.org/aktuelles/staatsanwalt.de.html ** ODEM.org-Tour: http://tour.odem.org/ ** Informationsgesellschaft: http://www.wsis-koordinierungskreis.de/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (FreeBSD) iD8DBQE/zRsrOndlH63J86wRAtBIAKDOPE40+WLyFIK2mCT02gGDkW+YfwCguNcQ MzNwpNhheDC9fMcctqmA76Q= =nksO -END PGP SIGNATURE- Wenn Sie Ihr Abonnement fuer diese Gruppe kuendigen moechten, senden Sie eine E-Mail an: [EMAIL PROTECTED] Die Nutzung von Yahoo! Groups ist Bestandteil von http://de.docs.yahoo.com/info/utos.html ---(end of broadcast)--- TIP 3: 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] Validity check in to_date?
Sweeet!
This is what I ended up with:
create domain testdate char(10) check (VALUE::text::date = VALUE);
(it wasn't possible to insert a NULL date with (VALUE::text::date IS NOT
NULL); )
I'm pretty happy with this as I didn't even have to use
to_char(VALUE::text::date , '-MM-DD') for the comparison, since the
date format I am using matches the "datestyle" setting.
It seems to accept any valid date, as well as NULL, while rejecting inputs
such as:
insert into test1 values ('2002-03-32');
insert into test1 values ('200-03-22');
insert into test1 values ('2002- 3-22');
insert into test1 values ('2002-03-2');
insert into test1 values ('2002-03- 2');
insert into test1 values ('2002-3-2');
insert into test1 values ('2002-14-02');
insert into test1 values ('');
insert into test1 values ('2002/03/22');
insert into test1 values ('2002/03/32');
insert into test1 values ('200/03/22');
insert into test1 values ('2002/ 3/22');
insert into test1 values ('2002/03/2');
insert into test1 values ('2002/03/ 2');
insert into test1 values ('2002/3/2');
insert into test1 values ('2002/14/02');
It's no silk purse, but it's short and sweet and I'm satisfied.
Thanks guys.
Rregards
Iain
- Original Message -
From: "Stephan Szabo" <[EMAIL PROTECTED]>
To: "Iain" <[EMAIL PROTECTED]>
Cc: "Karel Zak" <[EMAIL PROTECTED]>; "Christoph Haller" <[EMAIL PROTECTED]>;
"Alexander M. Pravking" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Wednesday, December 03, 2003 1:15 AM
Subject: Re: [SQL] Validity check in to_date?
> On Tue, 2 Dec 2003, Iain wrote:
>
> > T've been following this thread with interest because I have a related
> > problem. Basically we are storing dates in CHAR fields with al the
> > associated problems. I'd like to do it, but changing everything to date
> > fields isn't practical for now, so as a stopgap solution, I want to
provide
> > some validation at the database level.
> >
> > I tried:
> >
> > create domain ymdtest2 as char(10) constraint valid_date check
> > (VALUE::DATE);
> >
> > But it gives this error:
> >
> > ERROR: cannot cast type character to date
> >
> > I also tried:
> >
> > create domain test char(10) check (CAST(VALUE AS DATE));
>
> I'd try CAST(CAST(VALUE AS TEXT) AS DATE)
>
> There's a text->date conversion, but not one from character(n).
>
>
> ---(end of broadcast)---
> TIP 6: Have you searched our list archives?
>
>http://archives.postgresql.org
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Updating session id based on accesstimeout
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 > I want to set a session id based on a session timeout per user. I > have a very large logfile with more than 1.3 Mio records and look > for a very efficient solution - maybe with PL/pgSQL . Can you post a clearer example with the exact columns in the table, and what you wish to do? - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200312022156 -BEGIN PGP SIGNATURE- iD8DBQE/zVDsvJuQZxSWSsgRAnXaAJ9ndHTI7ha9ZyWS+Bnybgbve09jVQCffWTa H/csLQmY29QTReOH7XYMtHs= =7xo3 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
