ansaurus

Question

select query for time interval in mysql and php

Answer 1

+2 A:

Assuming you have a DATETIME column named created in your table:

SELECT id FROM tablename 
WHERE ((created > DATE_SUB(NOW(), INTERVAL 15 MINUTE))
  AND (created < NOW()))

Pekka 2010-07-08 08:57:31

What's the second condition for? To prevent against time traveling?

Artefacto 2010-07-08 08:59:15

@Artefacto you never know! :)

Pekka 2010-07-08 09:00:42

`if bool(foo) == True and True is True: print 'foo is True'`

shylent 2010-07-08 09:00:48

@shylent not true. It is perfectly possible to insert future dates into the `created` column. There is no native mySQL way to stop anybody from doing so. Better safe than sorry.

Pekka 2010-07-08 09:02:09

@Pekka: don't be so boring :) Although I do, obviously, see your point

shylent 2010-07-08 09:03:11

@shylent Finding and fixing a bug caused by something like this is *way* more boring! :)

Pekka 2010-07-08 09:04:12

Well, I've never said, that your query was wrong or redundant.

shylent 2010-07-08 09:06:10

@shylent I know. Just teasing back :)

Pekka 2010-07-08 09:08:18

thanks 4 help its working...INTERVAL 15 MINUTE i need to set this as a variable how can i do it?

mayuri 2010-07-08 10:22:11

@mayuri You mean as a PHP variable? Just insert it into your query, nothing special to it. As a mySQL field, I'm not sure it can be done.

Pekka 2010-07-08 10:23:21

i want the time to be changed dynamically later so i needed

mayuri 2010-07-08 10:33:09

@mayuri that is impossible to answer, as you are giving no information about the context you are working in. Check out constants in PHP, maybe they are what you need. http://www.php.net/constants

Pekka 2010-07-08 10:38:59

i want to fetch the records that were entered in last 15 mins, from that if the number of failures are more then 5 i want to block further login so i want that time i e 15 minutes to change later so need to make it dynamic..forgive for my english

mayuri 2010-07-08 10:45:44

@mayuri Put it in a variable? I don't know what your question is.

Pekka 2010-07-08 10:47:13

do you mean $var1=INTERVAL 15 MINUTE its nt correct..

mayuri 2010-07-08 10:49:14

can i use between?

mayuri 2010-07-08 10:50:26

@PEKKA SELECT status FROM `login_attempts` WHERE lastlogin BETWEEN NOW() AND 1500 1500 is minutes in seconds is this correct can i use this?please help its urgent

mayuri 2010-07-08 11:06:10

@mayuri why 1500? What is wrong with INTERVAL 15 MINUTE?

Pekka 2010-07-08 11:06:54

my boss want it to be dynamic to change later so.. i dont have any problem...

mayuri 2010-07-08 11:08:24

sorry for eating ur time and annoying u..m new to this..

mayuri 2010-07-08 11:09:40

@mayuri just store `$variable = "INTERVAL 15 MINUTE";` and use `$variable` in the query. It won't make much sense though as you can change it in the code anyway. The usual way is to define a constant in the top of the file: `define("QUERY_INTERVAL", "15 MINUTE");` and then to use the constant in the query: "SELECT ..... BETWEEN (NOW(), INTERVAL".QUERY_INTERVAL.")");` Constants are explained in the link I posted above.

Pekka 2010-07-08 11:11:26

thanks very very its working......

mayuri 2010-07-08 11:15:19

Answer 2

+1 A:

select * from table WHERE datetimeField BETWEEN date_add(NOW(),INTERVAL -15 MINUTE) AND NOW();

Māris Kiseļovs 2010-07-08 09:02:11

+1 for using `BETWEEN`, it's the better solution of course!

Pekka 2010-07-08 10:07:19

ansaurus

tags:

views:

answers:

select query for time interval in mysql and php

related questions