 | |
Portable SQL to compute a date difference in seconds???
comp.databases comp.databases
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Portable SQL to compute a date difference in seconds??? -
05-11-2007
, 02:21 AM
#2
| |||
| |||
|
|
Hi all, I am trying to get a portable SQL statement that returns the difference between 2 dates as a number of seconds. Is there any possibility to use standard SQL functions to achieve this? In PostgreSQL one can use: extract(epoch from (date1 - date2)) In MySQL one can use: datediff(date1,date2) * 86400 In Access one can use: DateDiff("s",date1,date2) etc... None of the above pleases me - I'ld like a common expression for all these databases. Regards, Johan |
it doesn't mean all are the same or even similar.
Gints Plivna
http://www.gplivna.eu
#3
| |||
| |||
|
|
I am trying to get a portable SQL statement that returns the difference between 2 dates as a number of seconds. Is there any possibility to use standard SQL functions to achieve this? In PostgreSQL one can use: extract(epoch from (date1 - date2)) In MySQL one can use: datediff(date1,date2) * 86400 In Access one can use: DateDiff("s",date1,date2) etc... None of the above pleases me - I'ld like a common expression for all these databases. |
In Visual FoxPro, the difference between two dates is
date1-date2
For seconds, you could multiply by 86400 although this gives
nonsensical results as your data is not that precise.
You could use the datetime type. The difference between two
datetime values is in seconds, as in:
datetime1-datetime2
This could turn into the DBMS equivalent of programming "100
Bottles of Beer" in various programming languages.
Sincerely,
Gene Wirchenko
Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.
#4
| |||
| |||
|
|
You could use the datetime type. The difference between two datetime values is in seconds, as in: datetime1-datetime2 |
duration of an interval in seconds isn't standard either
On 11 mei, 18:39, Gene Wirchenko <g... (AT) ocis (DOT) net> wrote:
Quote:
|
This could turn into the DBMS equivalent of programming "100 Bottles of Beer" in various programming languages. |
Quote:
|
Time to understand that even if these products all are called DBMSes it doesn't mean all are the same or even similar. |
even for something as simple, common and basic as subtracting 2
dates...
The level of standardisation in SQL is below what I expected. :-(
An variation on the comment from Gints:
Time for the DBMS vendors to understand that their lack of
standardisation is costing people a lot of time and effort.
Johan
#5
| |||
| |||
|
|
On 11 mei, 18:39, Gene Wirchenko <g... (AT) ocis (DOT) net> wrote: You could use the datetime type. The difference between two datetime values is in seconds, as in: datetime1-datetime2 This subtraction returns a value of type 'interval'. Getting the duration of an interval in seconds isn't standard either |
(datetime1-datetime2) SECOND
Quote:
|
On 11 mei, 16:42, Gints Plivna <gints.pli... (AT) gmail (DOT) com> wrote: Time to understand that even if these products all are called DBMSes it doesn't mean all are the same or even similar. Understood... and confirms my suspicion - no general solution possible even for something as simple, common and basic as subtracting 2 dates... |
{fn timestampdiff(sql_tsi_second,datetime1,datetime2)}
However, ODBC and JDBC are each controlled by single vendors.
Quote:
|
The level of standardisation in SQL is below what I expected. :-( An variation on the comment from Gints: Time for the DBMS vendors to understand that their lack of standardisation is costing people a lot of time and effort. |
you into a single product. This is true for the major vendors and for
many open source products
--
Lee Fesperman, FFE Software, Inc. (http://www.firstsql.com)
================================================== ============
* The Ultimate DBMS is here!
* FirstSQL/J Object/Relational DBMS (http://www.firstsql.com)
#6
| |||
| |||
|
|
Understood... and confirms my suspicion - no general solution possible even for something as simple, common and basic as subtracting 2 dates... The level of standardisation in SQL is below what I expected. :-( |
differences among various DB and these are much more important than
just some syntactic problems.
If you don't care about them then either
1) you are creating application that won't work correctly on at least
some of your DB's
2) you are using the very very basic features of each DB and so your
customers will waste much $$ gathering licences for thousands of bucks
and using only 1% of features they bought
OK there is also third way - you are creating an app that has unique
software level for each DB but then your app is very big, very
expensive and you have developed it over many years
Quote:
|
An variation on the comment from Gints: Time for the DBMS vendors to understand that their lack of standardisation is costing people a lot of time and effort. |
Ferrari and put it into Trabant. Each model is predicted for different
needs and resources, each is manufactured by different company,
developed by different people thinking differently, each have his own
history, they'll never be equal.
And that's why we have alternatives, that's why the life is
interesting
Gints Plivna
http://www.gplivna.eu
#7
| |||
| |||
|
|
The level of standardisation in SQL is below what I expected. :-( The problem actually is much bigger - there are many architectural differences among various DB and these are much more important than just some syntactic problems. If you don't care about them then either 1) you are creating application that won't work correctly on at least some of your DB's 2) you are using the very very basic features of each DB and so your customers will waste much $$ gathering licences for thousands of bucks and using only 1% of features they bought |
and are of less concern. More important is the effect on application
software. Here syntactic/semantic differences are pretty important.
The only major one outside of that is tuning SQL commands, and that
one is pretty major.
Quote:
|
An variation on the comment from Gints: Time for the DBMS vendors to understand that their lack of standardisation is costing people a lot of time and effort. There are many cars around an you cannot simply get engine from Ferrari and put it into Trabant. Each model is predicted for different needs and resources, each is manufactured by different company, developed by different people thinking differently, each have his own history, they'll never be equal. And that's why we have alternatives, that's why the life is interesting |
engines. Here the user experience is easier --- you can probably jump
in most cars and start driving right away. Unfortunately, humans are
much more flexible than software ;^)
Also for DBMSs, a standard (ANSI SQL) exists ... that is willfully
ignored by vendors. In addition, there is the Relational Model (RM),
mostly ignored by vendors too. It's RM where tuning is a problem.
Improper, arcane optimization severely impacts relational power.
--
Lee Fesperman, FFE Software, Inc. (http://www.firstsql.com)
================================================== ============
* The Ultimate DBMS is here!
* FirstSQL/J Object/Relational DBMS (http://www.firstsql.com)
#8
| |||
| |||
|
|
Gints Plivna wrote: The level of standardisation in SQL is below what I expected. :-( The problem actually is much bigger - there are many architectural differences among various DB and these are much more important than just some syntactic problems. If you don't care about them then either 1) you are creating application that won't work correctly on at least some of your DB's 2) you are using the very very basic features of each DB and so your customers will waste much $$ gathering licences for thousands of bucks and using only 1% of features they bought Agree. There are many architectural differences. Most only affect DBAs and are of less concern. More important is the effect on application software. Here syntactic/semantic differences are pretty important. The only major one outside of that is tuning SQL commands, and that one is pretty major. |
or even data manipulation.
#9
| |||
| |||
|
|
Gints Plivna wrote: The level of standardisation in SQL is below what I expected. :-( The problem actually is much bigger - there are many architectural differences among various DB and these are much more important than just some syntactic problems. If you don't care about them then either 1) you are creating application that won't work correctly on at least some of your DB's 2) you are using the very very basic features of each DB and so your customers will waste much $$ gathering licences for thousands of bucks and using only 1% of features they bought Agree. There are many architectural differences. Most only affect DBAs and are of less concern. More important is the effect on application software. Here syntactic/semantic differences are pretty important. The only major one outside of that is tuning SQL commands, and that one is pretty major. An variation on the comment from Gints: Time for the DBMS vendors to understand that their lack of standardisation is costing people a lot of time and effort. There are many cars around an you cannot simply get engine from Ferrari and put it into Trabant. Each model is predicted for different needs and resources, each is manufactured by different company, developed by different people thinking differently, each have his own history, they'll never be equal. And that's why we have alternatives, that's why the life is interesting Disagree. A better analogy might be switching cars rather than engines. Here the user experience is easier --- you can probably jump in most cars and start driving right away. Unfortunately, humans are much more flexible than software ;^) |
Ever get a rental car where the Lights switch was in a different
place? Wipers? Two basic safety features are not standard on cars.
Getting a standard transmission is also a significant challenge for
some drivers, just as changing the locking model of the DB engine is a
challenge to some programmers. And it takes an analogous period of
time to adjust from one car to another as it does from one DBMS to
another. Unfortunately, it is the flexibility of humans doing the
programming that continues to allow this situation to exist.
Quote:
|
Also for DBMSs, a standard (ANSI SQL) exists ... that is willfully ignored by vendors. In addition, there is the Relational Model (RM), mostly ignored by vendors too. It's RM where tuning is a problem. Improper, arcane optimization severely impacts relational power. |
Does the SQL standard really specify Date and Time processing? From my
understanding (second hand since I have not read the actual standards
documents) is that Date and Time data types are purposely vague. This
allows vendors with widely different implementations to claim
compliance. This doesn't help the original person, but it is how it
is.
I'm actually dealing with the date time issue right now, since I work
with both UNIFY Dataserver (Separate DATE and TIME data types) and
ORACLE (DATE data type includes time) and have to move data between
the two. It can get tedious, but we deal with it.
Ed
#10
| |||
| |||
|
|
On May 15, 7:01 pm, Lee Fesperman <first... (AT) ix (DOT) netcom.com> wrote: |
Quote:
|
Disagree. A better analogy might be switching cars rather than engines. Here the user experience is easier --- you can probably jump in most cars and start driving right away. Unfortunately, humans are much more flexible than software ;^) Yes, the analogy is better but the same issues exist. Ever get a rental car where the Lights switch was in a different place? Wipers? Two basic safety features are not standard on cars. |
cap is on. I am getting better at remembering this detail, but:
I usually rent for a few days at a time. I will fill up once,
maybe, twice while I have the car. It is something that I do not have
to think about until it is time to fill up. Too many times, I have
pulled up to a gas pump and then realised that I pulled up the wrong
way with yet another car.
[snip]
Sincerely,
Gene Wirchenko
Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.