?
Solved

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

Posted on 2009-03-30
8
Medium Priority
?
2,676 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
Industry Leaders: 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!

 

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 1000 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Microsoft Jet database engine errors can crop up out of nowhere to disrupt the working of the Exchange server. Decoding why a particular error occurs goes a long way in determining the right solution for it.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses

621 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