[Firebird-devel] CORE-1526 - Wrong syntax DATEADD and DATEDIFF functions

Post by Adriano dos Santos Fernandes
---------
DATEDIFF(WEEKDAY, x, y)
DATEDIFF(WEEKDAY, x, y)
As add the weekday to date?
DATEDIFF(WEEK, x, y)
DATEDIFF(WEEK, x, y)?
---------

IMO, there's no sense to add/substract a weekday to/from a datetime
value, so we should just forbid this syntax. The same for yearday.

As for ISO week numbers, the implementation could just replace <n> weeks
with <7 * n> days, but I'm not really sure we should follow that path.

I would prefer DATEADD/DATEDIFF to deal only with the INTERVAL datetime
parts as defined by the SQL specification, maybe with just one
extention: MILLISECOND.

Dmitry

Martijn Tonies

2007-10-22 14:47:46 UTC

Post by Dmitry Yemanov

Post by Adriano dos Santos Fernandes
---------
DATEDIFF(WEEKDAY, x, y)
DATEDIFF(WEEKDAY, x, y)
As add the weekday to date?
DATEDIFF(WEEK, x, y)
DATEDIFF(WEEK, x, y)?
---------

IMO, there's no sense to add/substract a weekday to/from a datetime
value, so we should just forbid this syntax. The same for yearday.

It depends on what it actually means. If it's just a number saying "1", no
it doesn't make sense.

But think of trying to get "the next monday from todays date", would that
work? Or be made to work?

Post by Dmitry Yemanov
As for ISO week numbers, the implementation could just replace <n> weeks
with <7 * n> days, but I'm not really sure we should follow that path.
I would prefer DATEADD/DATEDIFF to deal only with the INTERVAL datetime
parts as defined by the SQL specification, maybe with just one
extention: MILLISECOND.

Having INTERVAL available would be nice :-)

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

Adriano dos Santos Fernandes

2007-10-22 14:58:17 UTC

Post by Dmitry Yemanov
I would prefer DATEADD/DATEDIFF to deal only with the INTERVAL datetime
parts as defined by the SQL specification, maybe with just one
extention: MILLISECOND.

Agreed. Let's remove this.

User can add "7 * n" days.

Adriano

Martijn Tonies

2007-10-22 15:01:47 UTC

Post by Dmitry Yemanov
I would prefer DATEADD/DATEDIFF to deal only with the INTERVAL datetime
parts as defined by the SQL specification, maybe with just one
extention: MILLISECOND.

Agreed. Let's remove this.
User can add "7 * n" days.

Direct math on date(/time) values is a hack, no matter how you look at it.

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

Adriano dos Santos Fernandes

2007-10-22 15:06:09 UTC

Post by Dmitry Yemanov
I would prefer DATEADD/DATEDIFF to deal only with the INTERVAL datetime
parts as defined by the SQL specification, maybe with just one
extention: MILLISECOND.

Agreed. Let's remove this.
User can add "7 * n" days.

Direct math on date(/time) values is a hack, no matter how you look at it.

Ah, didn't you never multiplied integers in your programs? :-)

What hack has in this?
select dateadd(day, 7 /* 1 * 7 */, current_date) next_week from
rdb$database

Adriano

Martijn Tonies

2007-10-22 15:10:10 UTC

Post by Dmitry Yemanov
I would prefer DATEADD/DATEDIFF to deal only with the INTERVAL datetime
parts as defined by the SQL specification, maybe with just one
extention: MILLISECOND.

Agreed. Let's remove this.
User can add "7 * n" days.

Direct math on date(/time) values is a hack, no matter how you look at it.

Ah, didn't you never multiplied integers in your programs? :-)

"integer" is defined as such. We both can do integer math, no problem.

Post by Adriano dos Santos Fernandes
What hack has in this?
select dateadd(day, 7 /* 1 * 7 */, current_date) next_week from
rdb$database

Date values are not defined as "integer" values and any math you do on
them is "implementation defined" and, basically, subject to change without
notice. I'm sure the Firebird Project won't change it due to backwards
compatibility, but the point stands.

Post by Martijn Tonies
Direct math on date(/time) values is a hack, no matter how you look at it.

Having a set of functions that avoids this is as it's supposed to be.

Now, coming back to your statement about never having integers multiplied
etc, in real life, what do you do if you want to know tomorrows date? Do you
just do "+ 1"? No, you don't. Which proves my point.

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

Adriano dos Santos Fernandes

2007-10-22 15:15:03 UTC

Martijn, read my code again. :-)

Where do you see DATE + INTEGER?

We humans multiply things, 1 day * 7 = 7 days, and 1 week is 7 days in
"our" calendar. -- day is not date :-)

DATEADD is who sum date with the integer, you should not worry about it.

Adriano

Post by Dmitry Yemanov

Post by Dmitry Yemanov
I would prefer DATEADD/DATEDIFF to deal only with the INTERVAL

datetime

Post by Dmitry Yemanov
parts as defined by the SQL specification, maybe with just one
extention: MILLISECOND.

Agreed. Let's remove this.
User can add "7 * n" days.

Direct math on date(/time) values is a hack, no matter how you look at

it.

Post by Adriano dos Santos Fernandes
Ah, didn't you never multiplied integers in your programs? :-)

"integer" is defined as such. We both can do integer math, no problem.

Post by Adriano dos Santos Fernandes
What hack has in this?
select dateadd(day, 7 /* 1 * 7 */, current_date) next_week from
rdb$database

Post by Martijn Tonies
Direct math on date(/time) values is a hack, no matter how you look at

it.
Having a set of functions that avoids this is as it's supposed to be.
Now, coming back to your statement about never having integers multiplied
etc, in real life, what do you do if you want to know tomorrows date? Do you
just do "+ 1"? No, you don't. Which proves my point.

Martijn Tonies

2007-10-22 15:17:48 UTC

Post by Adriano dos Santos Fernandes
Martijn, read my code again. :-)
Where do you see DATE + INTEGER?
We humans multiply things, 1 day * 7 = 7 days, and 1 week is 7 days in
"our" calendar. -- day is not date :-)

Woops, sorry about that, I'm doing several things at the same time, I
shouldn't
multitask today :-)

Post by Adriano dos Santos Fernandes
DATEADD is who sum date with the integer, you should not worry about it.

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

Leyne, Sean

2007-10-22 22:03:15 UTC

Post by Martijn Tonies
Woops, sorry about that, I'm doing several things at the same time, I
shouldn't multitask today :-)

Not unless Arno let you sleep all the way home from Hamburg...

If you didn't, I wouldn't recommend you go near a PC for another couple
of days. ;-0

Sean

Martijn Tonies

2007-10-23 08:22:37 UTC

Post by Leyne, Sean

Post by Martijn Tonies
Woops, sorry about that, I'm doing several things at the same time, I
shouldn't multitask today :-)

Not unless Arno let you sleep all the way home from Hamburg...
If you didn't, I wouldn't recommend you go near a PC for another couple
of days. ;-0

:-)

I'll take it it's not much better with you then, Sean?

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

Leyne, Sean

2007-10-23 14:44:54 UTC

Post by Leyne, Sean

Post by Martijn Tonies
Woops, sorry about that, I'm doing several things at the same time, I
shouldn't multitask today :-)

Not unless Arno let you sleep all the way home from Hamburg...
If you didn't, I wouldn't recommend you go near a PC for another couple
of days. ;-0

:-)
I'll take it it's not much better with you then, Sean?

Fortunately, by traveling one day later, I was able to "recharge" by:

- sleeping 15 of the 24 hours left before my departure
- having a VERY long hot shower at the hotel
- doing nothing on my 8 hour flight to Toronto except watch movies...

All of which was my original plan, when I booked my flight for the
Monday.

Had I traveled on Sunday, I would be nothing more than a walking coma
patient, drooling from my mouth...

God! We need to do that again soon!!

Sean

Kovalenko Dmitry

2007-10-22 15:08:24 UTC

Post by Dmitry Yemanov
I would prefer DATEADD/DATEDIFF to deal only with the INTERVAL datetime
parts as defined by the SQL specification, maybe with just one
extention: MILLISECOND.

Agreed. Let's remove this.
User can add "7 * n" days.

Aga.

User can also write code like

TIMESTAMPDIFF(SQL_TSI_WEEK, X, Y) =
(MOD(EXTRACT(WEEKDAY FROM x),7) + datediff(day,x,y) - MOD(EXTRACT(WEEKDAY FROM y),7)) / 7

for calculating of diff of weeks

Thank you 8-[]

Kovalenko Dmitry
www.ibprovider.com

Richard Wesley

2007-10-22 15:42:55 UTC

Post by Dmitry Yemanov
As for ISO week numbers, the implementation could just replace <n> weeks
with <7 * n> days, but I'm not really sure we should follow that path.

Since the T-SQL function you appear to be copying has exactly this
behaviour I would agree.

Adriano dos Santos Fernandes

2007-10-22 15:46:56 UTC

Post by Richard Wesley

Post by Dmitry Yemanov
As for ISO week numbers, the implementation could just replace <n> weeks
with <7 * n> days, but I'm not really sure we should follow that path.

Since the T-SQL function you appear to be copying has exactly this
behaviour I would agree.

Richard Wesley

2007-10-22 16:09:33 UTC

It's returning NULL in HEAD. The fix was done after beta2.
Did you tried a snapshot build as I commented here?

I tried 2.1b2 which was released shortly after your response. Since
all the numeric functions were fixed in b2, I guess I assumed they
were all done at once and this one was an oversight. But it sounds
like that is not the case?

Best regards,
________________________________________________________
Richard Wesley Senior Software Developer Tableau
Software
Visit: http://www.trytableau.com/now.html

Adriano dos Santos Fernandes

2007-10-22 17:07:27 UTC

Post by Richard Wesley

It's returning NULL in HEAD. The fix was done after beta2.
Did you tried a snapshot build as I commented here?

No, the tree was tagger for beta2 some time ago.

Adriano

Richard Wesley

2007-10-22 20:16:46 UTC

Post by Adriano dos Santos Fernandes
No, the tree was tagger for beta2 some time ago.

I grabbed snapshot 16931 and it all looks good. thanks.
________________________________________________________
Richard Wesley Senior Software Developer Tableau
Software
Visit: http://www.trytableau.com/now.html

Paul Beach

2007-10-23 14:53:13 UTC

Post by Leyne, Sean
- sleeping 15 of the 24 hours left before my departure
- having a VERY long hot shower at the hotel
- doing nothing on my 8 hour flight to Toronto except watch movies...
All of which was my original plan, when I booked my flight for the
Monday.
Had I traveled on Sunday, I would be nothing more than a walking coma
patient, drooling from my mouth...
God! We need to do that again soon!!

Poul Dige

2007-10-23 15:57:11 UTC

Well - at least from 9.30 am to 2 am the following day is about the only thing in this thread that has something to do with DATEDIFF :)

Poul

----- Original Message -----
From: "Paul Beach" <***@ibphoenix.com>
To: <firebird-***@lists.sourceforge.net>
Sent: Tuesday, October 23, 2007 4:53 PM
Subject: Re: [Firebird-devel] CORE-1526 - Wrong syntax DATEADD and DATEDIFF

Post by Paul Beach

Nope - not at least until next year... :-)
I left the hotel for the airport at about 9.30am
Flew from Hamburg to Paris, then picked up
a TGV from the airport to Tours at about 18.30pm
About 30 minutes into the journey we stopped at the south
west side of Paris only to learn that the line ahead was
closed because of a "personal accident at Vendome"
We wait, and then get a large detour to Angers, where
I swop trains for another back to Tours.
The upshot of this is I get home at 2.00am in the morning.
I have to confess to being pretty tired when I got in.
Paul
Paul Beach
Tel (France): +33 (0) 2 47 58 30 43
Mob (France): +33 (0) 6 79 24 32 32
-------------------------------------------------------------------------
This SF.net email is sponsored by: Splunk Inc.
Still grepping through log files to find problems? Stop.
Now Search log events and configuration files using AJAX and a browser.
Download your FREE copy of Splunk now >> http://get.splunk.com/
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel

Poul Dige

2007-10-28 15:48:44 UTC

(FB 2.1.0.15999)
In a somewhat complex database I have reached the above error message because I have a few calculated fields in a table and/or in a view.

The DB is not in any way extravagant as it is about 10 calculated fields we are talking about (I was forced to operate with calculated fields because we can't yet use joins with SP's in a view which would have been my preferred way to do it, but as I understand that matter might eventually be looked into).

I must admit that the query plan is getting a little complex, but still a "select * from table" is no longer possible.. I can, however, select any reasonable subset of the fields in the table or view without problems, as long as I keep the resulting query plan within the allowed limits.

Is this a limitation that we are going to break any time soon? Or maybe even something to be considered as a flaw?

(I am using IB-Expert, but I guess that's not where the problem is originated?)

Regards
Poul Dige
Tabulex

Dmitry Yemanov

2007-10-28 19:58:37 UTC