?
Solved

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

Posted on 2009-03-30
8
Medium Priority
?
2,750 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
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 

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:
Nick Upson 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.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

568 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