datediff

MySQL joining four tables and using WEEK and DATEDIFF to get current data?

Hi! I'm trying to write a query getting information from four tables and I'm not able to figure it out. Can someone please help me with some guidance? The model is that a user can book a room for a specified time period. I would like to retrieve the data that is current for the next seven days so I'm using WEEK and DATEDIFF. The DATEDI...

Inconsistent MySQL DATEDIFF behavior

When I use this query: SELECT COUNT(*) FROM `my_table` WHERE DATEDIFF(NOW(), updated) > 2 MySQL executes the query with no errors and I get the count of rows that have not been updated within the last 2 days. However, if I change the query like this: SELECT * FROM `my_table` WHERE DATEDIFF(NOW(), updated) > 2 I get the following e...

Finding data closest to date?

I have a table CREATE TABLE `symbol_details` ( `symbol_header_id` int(11) DEFAULT NULL, `DATE` datetime DEFAULT NULL, `ADJ_NAV` double DEFAULT NULL ) with ~20,000,000 entries. Now I want to find the ADJ_NAV value closest to the end of the quarter for just one symbol_header_id: SET @quarterend = '2009-3-31'; SELECT symbol_head...

SQL DATEDIFF over non contiguous dates

I've got a table that looks like this: Code Mark Date Notional Beta 5/1/2008 $1,102,451.00 Beta 5/2/2008 $1,102,451.00 Beta 5/5/2008 $1,102,451.00 Beta 5/6/2008 $1,102,451.00 I need to create a table that has all of the Mark Dates in one column and the difference between each adjacent Mark Date when sorted in another co...

SQL Time Tracking Query

Data: EmpNumber, TimeStamp, AreaName 10632, 2009-11-23 16:40:33.000, OUT_1 10632, 2009-11-23 16:39:03.000, IN_1 10632, 2009-11-23 16:38:56.000, IN_1 10632, 2009-11-23 15:31:51.000, OUT_1 10632, 2009-11-23 15:31:48.000, IN_1 10632, 2009-11-23 15:31:43.000, IN_1 10632, 2009-11-23 15:31:14.000, OUT_1 1063...

Sharepoint Designer - is there a way to calculate a date difference in a workflow?

Maybe I'm just crazy... So, with SPD I can add any time unit to a date and put that into a variable, but I can't seem to find anything that will let me calculate the difference between two dates. "Do Calculation" only lets me pick from numeric values. Am I missing something here? Seems like something which should be obvious. ...

How to use DATEDIFF? How many days are inside of two dates.

How to use DATEDIFF? How can I make this to work? or should I use DATEDIFF completly differently? SELECT DATEDIFF('Started ','will_end') AS 'Duration' FROM my_table WHERE id = '110'; I try to get answer, how many days are inside of two dates. I would like to get an aswer like: Duration = 7 days; I have this kind of database: Start...

Coldfusion, Compare two TimeStamp to get the difference in Hours

Hello... I'm going have SELECT a TIMESTAMP from the MySQL database. What I want to do is compare that to the current time using ColdFusion. If 1+ from the DIFF do this.... If LT 1hr do this.... Thanks ...

Datediff between non-consecutive rows in a table

I would like to take the average of the time difference from Table1 below. The values are not consecutive and occasionally the time value is repeated, so I need to 1) sort by time, 2) discard non-unique values, 3) perform the time difference (in milliseconds), then 4) average the resulting time difference values. Further I'd like to 5)...

how to get the number on months between two dates in sql server 2005

i have a column in my sql server 2005 table that should hold the number of months an employee has been in service. Since i also have the date the employee was engaged, i want the "months_In_Service" column to be a computed column. now if i use datediff(month,[DateEngaged],getdate()) as the formula for the months in service computed colum...

How do I get the difference (in seconds) between 2 dates without using: strtotime, the Zend Framework or a PEAR package?

Hello good people! :) As the title states, I want to get the difference (in seconds) between 2 (specifically between now and a date in the past) dates without using: strtotime, the Zend Framework or a PEAR package. I don't want to get into the details of my reason but the gist of it is that I'm working with very old dates (and I do mea...

SQL Query to aggregate DateDiff greater than given value

I have a logging table used for device “heartbeats”. I have these network devices that check-in/heartbeat with the server every 10 minutes. We are wanting statistics on when they miss their scheduled check-in time. I have a query that can do this on a per-device basis, but I need it to be modified to handle across all devices. The he...

SQL Question: Getting Datediff in days elapsed for each record in a group.

Given this table: How can I get the datediff in days between each status_date for each group of ID_Number? In other words I need to find the number of elapsed days for each status that the ID_Number has been given. Some things to know: All ID_Number will have a received_date which should be the earliest date for each ID_Number (bu...

MySQL query to evaluate aggregate duration of time something is "on"

I have a series of timestamped on/off data in a table, representing on/off states, or the point at which a state "starts" 00:00:00 0 04:00:00 1 08:00:00 0 09:00:00 1 15:00:00 0 20:00:00 1 23:59:59 0 I need to calculate the total duration of (say) the ON state over a 24h period. In this simplified example total du...

SQL DATEDIFF Not working!?

Hi all, I am running a simple DATEDIFF query but it doesn't seem to calculate the days properly or i'm doing something wrong. If I run PRINT DATEDIFF(Day, 2010-01-20, 2010-01-01) RETURN 19 Which is correct. If i change the month in the first date to Feb (02) I get something strange. PRINT DATEDIFF(Day, 2010-02-20, 2010-01-01) RETUR...

Is SQL DATEDIFF(year, ..., ...) an Expensive Computation?

I'm trying to optimize up some horrendously complicated SQL queries because it takes too long to finish. In my queries, I have dynamically created SQL statements with lots of the same functions, so I created a temporary table where each function is only called once instead of many, many times - this cut my execution time by 3/4. So my ...

Is there a way to subtract an amount of days from a date in SQL?

I know about DATEDIFF(d, date1, date2), but I am not looking to subtract two dates, rather an amount of days from a date. For example: "2010-04-13" - 4 = "2010-04-09" Is that possible with mySQL? ...

Find date difference in php?

Is there anyway to find the date difference in php? I have the input of from date 2003-10-17 and todate 2004-03-24. I need the results how many days is there within these two days. Say if 224 days, i need the output in days only. I find the solution through mysql but i need in php. Anyone help me, Thanks in advance. ...

How does DATEDIFF calculate week differences in SQL Server 2005?

I would like to calculate the difference in weeks between two dates, where two dates are considered part of the same week if their preceding Sunday is the same. Ideally, I'd like to do this using DATEDIFF, instead of learning an elaborate idiom to calculate the value. But I can't tell how it works when weeks are involved. The following ...

SQL Server DATEDIFF accuracy

I have to store some intervals in mssql db. I'm aware that the datetime's accuracy is approx. 3.3ms (can only end 0, 3 and 7). But when I calculate intervals between datetimes I see that the result can only end with 0, 3 and 6. So the more intervals I sum up the more precision I loose. Is it possible to get an accurate DATEDIFF in millis...