homepage Welcome to WebmasterWorld Guest from 54.226.192.202
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Order by timestamp WITH simple math calculation
neophyte

10+ Year Member



 
Msg#: 4475614 posted 12:04 pm on Jul 14, 2012 (gmt 0)

Hello All -

I'm having trouble with a particular select and hoping someone here can help.

I'm selecting a table of information and want to order it by a timestamp field (fld_approved).

That's easy.

The trick that I'm trying to preform however is that I want the results sorted by the timestamp value of fld_approved + 3 days AND are LESS THAN the current time.

As mentioned, fld_approved contains an int timestamp (like 1342178312) and the 259200 shown below is 60*60*24*3 seconds (the seconds for 3 days).

What I've got now - which doesn't work at all - is:

"SELECT *
FROM $tbl_foo
ORDER BY fld_approved
WHERE (fld_approved + 259200) < UNIX_TIMESTAMP
LIMIT 0, 10";

I keep getting an error on the WHERE clause and can't figure out where I'm going wrong... or if I'm going about this query in the wrong way altogether.

Any guidance greatly appreciated.

 

LifeinAsia

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4475614 posted 2:53 pm on Jul 14, 2012 (gmt 0)

You need to put the WHERE before the ORDER BY:
"SELECT *
FROM $tbl_foo
WHERE (fld_approved + 259200) < UNIX_TIMESTAMP
ORDER BY fld_approved
LIMIT 0, 10";

neophyte

10+ Year Member



 
Msg#: 4475614 posted 1:00 am on Jul 15, 2012 (gmt 0)

LifeinAsia -

Thanks very much... that should have been obvious to me.

Now, however, the error I'm getting is Unknown column 'UNIX_TIMESTAMP' in 'where clause'.

I would think that the WHERE clause is keying off fld_approved within the parens, but apparently not.

I've tried WHERE((fld_approved + 259200) < UNIX_TIMESTAMP) and other variations which would force the identification of fld_approved, but without any luck.

Any ideas?

Dijkgraaf

WebmasterWorld Senior Member 5+ Year Member



 
Msg#: 4475614 posted 1:26 am on Jul 15, 2012 (gmt 0)

UNIX_TIMESTAMP is a PHP command, so you need to either

1) execute that on the PHP side
"SELECT *
FROM $tbl_foo
WHERE (fld_approved + 259200) < " + UNIX_TIMESTAMP + "
ORDER BY fld_approved
LIMIT 0, 10";
2) replace UNIX_TIMESTAMP with NOW()

"SELECT *
FROM $tbl_foo
WHERE (fld_approved + 259200) < NOW()
ORDER BY fld_approved
LIMIT 0, 10";

My preference is for option 2.

neophyte

10+ Year Member



 
Msg#: 4475614 posted 1:51 am on Jul 15, 2012 (gmt 0)

Dijkgraaf -

Fantastic... have used option #2 and it works like a charm.

Thanks very much for the guidance!

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved