Solved

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

Posted on 2009-03-30
8
2,399 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
  • 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
 

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Introduction: Often, when running a query with joins, the results show up "duplicates", and often, those duplicates can be "eliminated" in the results using DISTINCT, for example. Using DISTINCT is simple: just add it after the SELECT keyword, an…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
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…

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now