Solved

How to get # of minutes difference between two Timestamp fields in Interbase 7.5?

Posted on 2009-03-30
8
2,543 Views
Last Modified: 2013-12-09
I'm working w/ Interbase 7.5 for the first time, and all I can say is I didn't realize what I was missing out on before.  I have a TIMESTAMP field, and the CURRENT_TIMESTAMP, and I'd like to know how many minutes has elapsed between the two.  DateDiff appears to only work on two dates.   I do know the difference between two timestamps is a numeric(x,y) #, w/ the integer part being the # of days and the decimals being fractions of the day.

The query tries to get the records created w/ in the last X number of minutes.

What would my WHERE clause look like for such a query?

SELECT *
FROM myTable
WHERE   (magical_timestamp_comparison_resuling_in_minutes_diff) > = 15


Thank you all in advance!
0
Comment
Question by:wtheronjones
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 14

Expert Comment

by:Daniel Junges
ID: 24027524
use datediff

datediff(MINUTE, CURRENT_TIMESTAMP, myField )
0
 

Author Comment

by:wtheronjones
ID: 24030321
Hi, Thanks for the reply but while that works in Sybase or SQL Server, I don't think that works in Interbase.  First, I get an error in ISQL about not having DATEDIFF or minute, and the DML definition statement to declare DATEDIFF shows that it takes 2 date arguments only, nothing for 'minute'

it's as follows:
DECLARE EXTERNAL FUNCTION DATEDIFF
DATE, DATE
RETURNS INTEGER
ENTRY_POINT 'fn_datediff' MODULE_NAME 'ib_udf';

0
 
LVL 14

Expert Comment

by:Daniel Junges
ID: 24031113
can you use this dll on interbase? then add the follow function:

DECLARE external function diffdate
        timestamp, timestamp, integer
        returns integer by value
        entry_point  'fudlib_diffdate' module_name 'fudlib';

Returns the difference in time between the two timestamps. control specifies the units:
4 days
3 hours
2 minutes
1 seconds
0 milliseconds
0
Webinar: Choosing a MySQL HA Solution

Join Percona’s Principal Technical Services Engineer, Marcos Albe as he presents Choosing a MySQL High Availability Solution on Thursday, June 29, 2017 at 10:00 am PDT / 2:00 pm EDT (UTC-7).

 

Author Comment

by:wtheronjones
ID: 24032105
Hmmm... that is a good suggestion, but I'd like to avoid adding any new binaries to the client setup as I'm new to Interbase & don't want to introduce mystery setup procedures since I'm responsible for them.  The installation I'm working with doesn't appear to have it as I tried using this func as you gave it w/ no luck.
My current workaround, since I'm using .Net3.5, is to get the "ROWS 1 to UserDefinedX" and then use a LINQ query to filter the results by my desired timestamp comparison, which I will probably stick with if the solution is to add a DLL to the setup.
0
 
LVL 14

Expert Comment

by:Daniel Junges
ID: 24032171
why you not use Firebird?
we have migrated all our projects from sqlserver to Firebird
0
 

Author Comment

by:wtheronjones
ID: 24032428
I couldn't really tell you why they haven't upgraded to Firebird... I'm doing a consulting project on a legacy product & don't want to initiate any drastic change.
0
 
LVL 19

Accepted Solution

by:
NickUpson earned 250 total points
ID: 24076656
where ((CURRENT_TIMESTAMP - timestampfield ) * 1440) >= 15
0
 

Author Comment

by:wtheronjones
ID: 24196478
Thanks Nick, that's what I was looking for!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question