SQL Dates Query


,

I'm new here and new to SQL.

I have a phpbb_users table in my phpbb forums database. The table has a column user_lastvisit which uses a unix timestamp.

Does anyone know a query that will return all rows where user_lastvisit is > 20 days ago
Posted On: Monday 29th of October 2012 05:39:32 AM Total Views:  337
View Complete with Replies

RELATED TOPICS OF MYSQL PROGRAMMING LANGUAGE




Sql statement to return daily rate between start and end dates?

I am working on a reservation application and am trying to figure out how best to store rate information in the database. Ideally a start and end date and the rate will be entered for that range. My problem is in how to select the rate for each day entered It is possible there will be different rates for some of the days in the range queried. For example, in the database we have the following rate information: START END RATE 3/1/2009 3/10/2009 50 3/11/2009 3/20/2009 80 How do I return the rate for each day for a query which spans both ranges If I searched for rates from 3/9/2009 through 3/12/2009 I should get the following results: 3/9/2009 - 50 3/10/2009 - 50 3/11/2009 - 80 3/12/2009 - 80 What should my sql statement look like to do this Would it also be possible to get the rates, and then calculate the average, all in one sql statement Thank you for your time and help!!!
VIEWS ON THIS POST

207

Posted on:

Wednesday 24th October 2012
View Replies!

Help with update and dates query

hi again ! I was reviewing some of my code and I found this piece of code that Im not liking. The idea behind it its no inactivate anyone that has a difference > 60 from the last visit until now. The way it was being done atm was to "hit" the DB each time a user had to be updated. Not good for performance. So far my code looks like: Code: SELECT paciente.idPaciente, DATEDIFF(now(), MAX(citas.Fecha) ) FROM paciente LEFT OUTER JOIN citas ON paciente.idPaciente = citas.idPaciente WHERE paciente.Estado='En tratamiento' AND citas.Horaentrada!='00:00:00' group by idPaciente ORDER BY idPaciente,citas.Fecha Im getting correct results, but I cant use the DATEDIFF function to tell the query that I need those wich difference is >= 60. Something like Code: WHERE DATEDIFF(now(), MAX(citas.Fecha) ) >=60 AND ...
VIEWS ON THIS POST

132

Posted on:

Wednesday 24th October 2012
View Replies!

Substrings in Updates?

I have a column called full_name that contains name like 'amar ram patel'. I want to update the same table by breaking the name in 3 different values using space as delimiter and add it to the respective columns viz. name, middle_name and surname. I guess substring_index function can be used for the same. Any pointers
VIEWS ON THIS POST

114

Posted on:

Wednesday 24th October 2012
View Replies!

Need a query to return dates that are not in database

I need to query the database to find dates and time slots that are not in the database, so that i can find what days and times I have available for my job booking script. I have a 'DATE' (MYSQL DATE FORMAT) column and a column named 'TIME' that gets a number either 1 - 5 this defines time slots i later use php to output the time to a readable format.( for ordering reasons) My script is a scheduling and booking system, I just need an easier way to find what I have available rather than outputting the jobs in the database and finding gaps by human deduction.
VIEWS ON THIS POST

127

Posted on:

Wednesday 24th October 2012
View Replies!

Querying BETWEEN dates on a DATETIME field

I'm going round in circles not finding an answer on this. As usual it's probably easy enough when you know how. Here's the situation: I have a table of data (user), which includes (amongst other things) a signup date (joindate). My objective is to create a php script to output a list of months from the oldest entry in the table to the current month, listing how many signups for each month. Then as a second function, I'll make the months in the list clickable to pull up a list of the specific rows that signed up in that month. So I guess I need to know the syntax for something that does a COUNT in a GROUP BY but with conditions of BETWEEN x and y in the joindate field. To be specific for code's sake, lets simplify it to a table with structure: id (int); joindate(datetime); Can someone help me with 2 queries to run please 1. SELECT all the rows, grouped according to the month of sign up. 2. SELECT rows that fall in a specific month (i.e. BETWEEN 1st of the month and 1st of following month) help will be most appreciated.
VIEWS ON THIS POST

113

Posted on:

Wednesday 24th October 2012
View Replies!

Check dates and ranges

I am taking a vacation starting 8/30/2007 and returning on 9/30/2007. So I will be out of the office from 8/30/2007 -> 9/29/2007. If I submit a new request, I want to look in the database to see if the new request days do not overlap the above. For example: These requests should be flagged: Request 1 - 8/30/2007 -> 9/5/2007 Request 2 - 9/5/2007 -> 9/30/2007 Request 3 - 9/15/2007 -> 9/21/2007 .... These are acceptable requests: Request 5 - 9/30/2007 - 10/10/2007 (since 9/30/2007 is the day we will be returning) Request 6 - 8/28/2007 -> 8/29/2007 Request 7 - 10/15/2007 -> 10/18/2007 ... To see if the new request is the same as the request in the database is easy. How do I check for the other cases in sql
VIEWS ON THIS POST

127

Posted on:

Wednesday 24th October 2012
View Replies!

To list datas between 2 dates

how can i list all data between 2 dates for examle SELECT username FROM user where ( 07.01.2005 - 01.05.2007)
VIEWS ON THIS POST

213

Posted on:

Wednesday 24th October 2012
View Replies!

entered date between start and end dates (was "Best way to do this?")

My table has two columns: start_date and end_date. If one record has a start_date of 2007-06-01 and an end_date of 2007-06-15 and the user enters in 2007-06-07, what's the best way to write a query that will find records where the entered date is between the start and end dates
VIEWS ON THIS POST

191

Posted on:

Wednesday 24th October 2012
View Replies!

Query not returning right dates

this is the select query... Code: SELECT * FROM ml_games WHERE gamedatetime >= 2007-05-01 AND gamedatetime < 2007-06-01 ORDER BY gamedatetime DESC I'm trying to bassicaly return all rows that happened in the month 5/2007 (may 2007). if i take out the Code: gamedatetime < 2007-06-01 it returns columns after that date, but if theres entries on months coming after may it won't be returning the rows i want. There is no mysql error, just it seems to be some sort of problem with the logic with "AND" or something any ideas
VIEWS ON THIS POST

132

Posted on:

Wednesday 24th October 2012
View Replies!

What to use when inserting time/dates + how to select if date older than 5 days +..

, When I've asked around people tell me that I shouldn't store my dates and so on in varchar and that MySQL can insert them for me.. Here is how I insert the dates and how I remove the rows that are older than five days: PHP Code: //Thecurrenttime $date=date("H:i:sMj,Y"); //Thecurrentunixtimestamp $timestamp=time(); //Seeifthereisanyrowinthetemporarydatabasethatisolderthan $cutoff=time()-432000;//5daysagoinseconds mysql_query("DELETEFROMtemp_usersWHEREscript_received
VIEWS ON THIS POST

117

Posted on:

Wednesday 24th October 2012
View Replies!

Problem comparing 2 dates BETWEEN 2 dates

Hi all, I require a small help making a query work. I am making a hotel admin section where the main admin will have facility to close day(s) i.e. will not take any booking for those days. for that I am getting either 1 date for day close and Start-End date for multiple day close. I made the day close but can't figure out multiple day close. The query which i made for 1 day close is given below: Code: SELECT * FROM booking WHERE '".$Date1."' BETWEEN Arrival AND Departure but how can i do this with 2 dates. For example if user give 2 dates: Date1=2006-11-14 & Date2=2006-11-18. So here i need to know the bookings which are between Date1 & Date2 AND Between Arrival & Departure So if there is one booking of 17 November (Arrival : 2006-11-17 , Departure : 2006-11-18) It should give 1 row in result. I tried a few queries and last query i perfom is Code: SELECT * FROM booking BETWEEN '2006-11-10' AND '2006-11-16' AND BETWEEN Arrival AND Departure but can't figure it out. I think I am missing something somewhere.
VIEWS ON THIS POST

165

Posted on:

Wednesday 24th October 2012
View Replies!

MySQL Slow Log Dominated by Inserts and Updates

I put a lot of work into optimizing our database queries over the years, but I always focus on our select queries. Since we are an ecommerce store, the majority of the queries we do are selects from the product catalog and even though the SQL in the selects can be rather large, they are very quick. Now that those have been cleaned up for awhile, I am noticing that the queries that seem to really take the longest are the ones that write data -- inserts and updates. I went over the last 100 queries added to our mysqlslow.log file (queries that run 5 seconds or longer), and about 68 of them are updates and 19 are inserts. Now that I've conquered the select beast, what can I do to improve our other queries (or maybe the underlying performance issue) And for the record, 90% of those queries are overly pure inserts or updates without any advanced select intos or joins. Also, we moved to a new, dedicated server just last year, so I am hoping hardware isn't to blame here. Snip of My.cnf file (mostly defaults I imagine): Code: set-variable = read_buffer_size=2M set-variable = key_buffer=512M set-variable = max_connections=200 set-variable = max_allowed_packet=12M set-variable = table_cache=2048 set-variable = sort_buffer=5M set-variable = record_buffer=2M set-variable = net_buffer_length=16K set-variable = myisam_sort_buffer_size=10M set-variable = thread_concurrency=2 set-variable = tmp_table_size=512M # mysql 4 stuff set-variable = query_cache_limit=12M set-variable = query_cache_size=512M set-variable = query_cache_type=1 , Sorry - didn't get back in here over the weekend..... Batch updating could be part of the problem, and there might be a logjam in the buffer/cache, but I don't know for sure. Are these updates running from the webserver, or the database server Personally, I'm thinking there's a configuration problem either in mySQL itself, or in your database instance. The only times I've seen problems like that (though they weren't mySQL) were configuration problems. The problem I've resolved dealt with... Overzealous virus scanning (this was a networking issue as scanning was occurring when something left one box and hit the next on the same network)Permission problems on the filesystemindexes incorrectly built.Sorry I can't give you more guidance, but the problem doesn't seem to be the query, but something else entirely....
VIEWS ON THIS POST

95

Posted on:

Thursday 25th October 2012
View Replies!

check for between dates

I have two columns, Start_Date and End_Date in a table. I need to do a query to see if today is between them.... Is this the way to go about it select * from coupon where DATE() >= Start_DATE and DATE()
VIEWS ON THIS POST

124

Posted on:

Thursday 25th October 2012
View Replies!

Get results within 2 dates

Hi How can I return all the results within 2 dates PHP Code: $getdate="09-08-14"; PHP Code: SELECT*FROMdatesWHEREStart>='$getdate'ANDEnd
VIEWS ON THIS POST

104

Posted on:

Thursday 25th October 2012
View Replies!

Updates across multiple tables

Hi I want to create a simple SQL statement to update these fields for all tseeker records 1. Update the tseekers.DateRetired column to '2008-07-10 00:00:00' 2. Update the tmatches.MatchStatusID to '8' I tried this query but couldn't get it to work UPDATE tseekers, tmatches SET tseekers.DateRetired = '2008-07-10 00:00:00', tmatches.MatchStatusID = '8' WHERE tseekers.SeekerID = tmatches.SeekerID I then need to insert a new record in the table 'tmatchstatushistory' to record this action (but I can't get this to work either). INSERT INTO tmatchstatushistory (MatchID, MatchStatusID, HistoryDate, Comment) VALUES ('tmatches.MatchID', '8', '2008-07-10 00:00:00', 'AUTO-GENERATED: This Seeker has been retired.') WHERE tmatches.MatchID = tmatchstatushistory.MatchID AND tmatches.SeekerID = tseekers.SeekerID AND tseekers.DateRetired = '2008-07-10 00:00:00' LIMIT 1
VIEWS ON THIS POST

162

Posted on:

Thursday 25th October 2012
View Replies!

recurring dates

I'm trying to make a calendar with events that can recur daily, weekly, or monthly. I think I can do it like so... any recommendations or improvements Code: SELECT * FROM drinks_e WHERE (drink_e_date = CURDATE()) AND (recurring = 'once') OR ( (DATE(drink_e_date)
VIEWS ON THIS POST

107

Posted on:

Thursday 25th October 2012
View Replies!

Select MAX dates?

If I have an endDate and a revisedEndDate in my "PROJECTS" table How can I select the MAX endDate OR the revisedEndDate if it exists
VIEWS ON THIS POST

119

Posted on:

Monday 29th October 2012
View Replies!

Get difference betwee 2 dates and times

hello all i have 2 columns every column in the following format column1 2011-04-01 11:39:54 column2 2019-02-03 00:00:00 i want get difference between above data as following 2 days 11:39 how to do so i tried many functions but nothing works please advice what is the query doing that
VIEWS ON THIS POST

133

Posted on:

Monday 29th October 2012
View Replies!

Sql statement, comparing dates

I'm just about completely clueless when it comes to SQL and databases, but I've cobbled together a page that displays a list of pages based on their creation date. The table is called PageList and the creation date is called CreationDate. CreationDate is in this format - 4/1/2010. So right now, the SQL statement is Code: SELECT PageList.* FROM PageList ORDER BY CreationDate DESC; What I would like to do is rather than display ALL the entries, just display the entries for the last 90 days (hopefully with the option to 90 days before that, and before that, etc). I've found various references on the internet about dates, but none of them have worked the way I expected them to. I'm probably horribly mangling the code. Can anyone explain how to say "only get the entries from the last 90 days"
VIEWS ON THIS POST

153

Posted on:

Monday 29th October 2012
View Replies!

Mysql dates and time

I want to get the total hours between two dates. so far i have done this SELECT DATEDIFF(tbl_discharge.discharge_date, tbl_admit_room.admit_date ) * 24) as datediff , tbl_admit_room.admit_date, tbl_discharge.discharge_date FROM hospital.tbl_discharge INNER JOIN hospital.tbl_admit_room ON (tbl_discharge.admitID_FK = tbl_admit_room.admitID_PK) it is calculating the hours according to dates. not hours. like if there is 5 days differences it get 120 hrs. and ignores the rest hours of the days. Admit date 2010-09-20 05:00:00 Discharge Date 2010-09-20 15:00:00 i have the dates in this format in mysql database.
VIEWS ON THIS POST

139

Posted on:

Monday 29th October 2012
View Replies!