Solved

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

Posted on 2009-03-30
8
2,459 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
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…

777 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