selecting records younger than 24 hours using SQL query?

[Philipp80]

Hello,

I have a TIMESTAMP(14)-field in my db and I need a quick way to select records younger than 24 hours, WITHOUT using PHP to convert around. I'm pretty sure this one should be quite easy, played around with something like

SELECT fields FROM db WHERE 'timestamp'>NOW()-INTERVAL 1 DAY)

for hours right now (with DATE, EXTRACT, BETWEEN..AND etc.), and I either got error in my queries or bogus results.

 

[AlbertAguirre]

I cannot imagine a way that it could be done. If you found a way please advise. I would like to know.

Why cant you use PHP?

I would do this: (psuedo code)

1) $timeStampVar = today - 24 hours
2) select statement where timestamp > $timeStampVar

I know you wanted a non-php answer but I cannot imagine why that is so.

 

[danomac]

Have you tried something like:

SELECT `field` FROM `db` WHERE TIMESTAMP(NOW()) - `timestamp` < 86400;

86400 is the number of seconds in a day.

 

[r937]

where timestampcolumn
> date_add(current_timestamp, interval -24 hour)


rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[Philipp80]

Inspired by danomacs reply, I figured out the following code:

SELECT * FROM `tlog` WHERE `timestamp` > NOW()-86400;

which is a valid query, like r937 ones, but I have the same problem as with all the other valid queries I did before. All records are shown, even the ones older than 24 hours, therefore useless.

Is it possible that this has something to do with date format incompatibilities? The mysqlfield is YYYYMMDDHHMMSS. Or any other idea? This one is driving me crazy...

 

[r937]

did you try my query?

what is the actual datatype of your `timestamp` column?

what are some sample values? how did you enter them?



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[Philipp80]

r937, I have tried your query, same result
.
Datatype is TIMESTAMP, I don't enter data in this one, is done automatically when field 'login' is written into this table.

Some examples straight from phpmyadmin...

Id login timestamp
1 encephalon 20041215192821
2 test 20041215192947
3 encephalon 20041215193116

 

[r937]

well, i dunno, it works for me

create table Philipp80 
( id tinyint not null primary key auto_increment
, login varchar(11)
, myfield timestamp
);
insert into Philipp80 (login,myfield) values
 ('encephalon',20041215192821)
,('test      ',20041215192947)
,('encephalon',20041215193116)
,('yesterday ',20041217093700)
,('tomorrow  ',20041219093700)
;
select * from Philipp80
 where myfield 
     < date_add(current_timestamp, interval -24 hour)
;     
id,login,myfield
1,encephalon,20041215192821
2,test,20041215192947
3,encephalon,20041215193116
4,yesterday,20041217093700

select * from Philipp80
 where myfield 
     > date_add(current_timestamp, interval -24 hour)
;     
id,login,myfield
5,tomorrow,20041219093700

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[Philipp80]

Sh*t - the only problem was that the TIMESTAMP fieldname was 'timestamp', renamed it and suddenly everything worked fine. Thanks for your help guys!

 

[danomac]

Oops, I was thinking unix timestamp again... mine would have worked (probably, anyway) if it were a unix timestamp...