selecting fields older than x amount of days

SELECT * 
FROM cdr
WHERE DATEDIFF( NOW( ) , calldate ) =5


select fields  that have 5 days old

mysql group by

SELECT DISTINCT COUNT( * ) , phone
FROM calls
GROUP BY phone;

importing table to a database

mysql -p1982 dialer <dialer.sql

How to select records from last 24 hours using SQL?

up vote75down votefavorite 37	I am looking for a where clause that can be used to retrieve records for the last 24 hours? sql datetime shareimprove this question edited Nov 7 '13 at 16:08 phimuemue 13.9k24178 asked Dec 11 '09 at 14:49 Mike 5452912
	1   Do you have a timestamp field on these records? – Shawn Steward Dec 11 '09 at 14:52 add a comment

8 Answers

activeoldestvotes

up vote45down voteaccepted

SELECT * FROM table_name WHERE table_name.the_date > DATE_SUB(CURDATE(), INTERVAL 1 DAY) shareimprove this answer answered Dec 11 '09 at 14:54 Guillaume Flandre 5,19343148

1   If the records are stored as DATETIME, this will select all records from the previous date, disregarding the time part. Been run at 23:59:59, the query will return all records for the last 48 hours, not 24. – Quassnoi Dec 11 '09 at 15:02 8   If you want to select the last 24 hours from a datetime field, substitute 'curate()' with 'now()'. This also includes the time. – Haentz Apr 30 '11 at 6:11  add a comment

up vote209down vote

In MySQL: SELECT * FROM mytable WHERE record_date >= CURDATE() - INTERVAL 1 DAY In SQL Server: SELECT * FROM mytable WHERE record_date >= DATEADD(day, -1, GETDATE()) In Oracle: SELECT * FROM mytable WHERE record_date >= SYSDATE - 1 In PostgreSQL: SELECT * FROM mytable WHERE record_date >= NOW() - '1 day'::INTERVAL In Redshift: SELECT * FROM mytable WHERE record_date >= GETDATE() - '1 day'::INTERVAL In SQLite: SELECT * FROM mytable WHERE record_date >= datetime('now','-1 day') In MS Access: SELECT * FROM mytable WHERE record_date >= (Now - 1) shareimprove this answer edited Jan 11 at 20:30 Jeeped 32.4k112143 answered Dec 11 '09 at 14:52 Quassnoi 236k46394449

I try the query for mysql but if its 2AM, for example, i get the records from 00 - 02 AM. Two hours only instead of 24. Any ideas? – John Jul 15 '14 at 22:36 I very much like the solution provided here, just what I noted in MySQL is the performance of Guillaume Flandre solution was faster. – oneworld Jul 31 '14 at 0:36 Perfect answer! – Bertie Jul 23 '15 at 8:52 add a comment

up vote8down vote	MySQL : SELECT * FROM table_name WHERE table_name.the_date > DATE_SUB(NOW(), INTERVAL 24 HOUR) The INTERVAL can be in YEAR, MONTH, DAY, HOUR, MINUTE, SECOND For example, In the last 10 minutes SELECT * FROM table_name WHERE table_name.the_date > DATE_SUB(NOW(), INTERVAL 10 MINUTE) shareimprove this answer answered Feb 5 '13 at 23:06 Karthik Jayapal 15028
	add a comment

up vote7down vote	Which SQL was not specified, SQL 2005 / 2008 SELECT yourfields from yourTable WHERE yourfieldWithDate > dateadd(dd,-1,getdate()) If you are on the 2008 increased accuracy date types, then use the new sysdatetime() function instead, equally if using UTC times internally swap to the UTC calls. shareimprove this answer answered Dec 11 '09 at 14:54 Andrew 16.6k23568
	add a comment

up vote4down vote	in postgres, assuming your field type is a timestamp: select * from table where date_field > (now() - interval '24 hour'); shareimprove this answer answered Jun 20 '12 at 22:31 pisaruk 583914
	add a comment

up vote1down vote	select ... from ... where YourDateColumn >= getdate()-1 shareimprove this answer answered Dec 11 '09 at 14:51 Mladen Prajdic 11.8k22443
	add a comment

up vote0down vote	SELECT * FROM tableName WHERE datecolumn >= dateadd(hour,-24,getdate()) shareimprove this answer answered Dec 11 '09 at 14:53 Galwegian 32.7k982137
	add a comment

up vote0down vote

In SQL Server (For last 24 hours): SELECT * FROM mytable WHERE order_date > DateAdd(DAY, -1, GETDATE()) and order_date<=GETDATE()