The Little Guy Posted October 31, 2008 Share Posted October 31, 2008 I have a timestamp column, how can I get the timestamp with the closest time to the current time? Example: Now: 2008-10-31 15:23:00 In database: 2008-10-30 14:25:00 2008-10-29 08:54:00 2008-10-31 14:32:00 2008-10-29 22:10:00 The closest one to Now would be the 3rd one. Link to comment https://forums.phpfreaks.com/topic/130938-newest-date/ Share on other sites More sharing options...
jwilliam Posted October 31, 2008 Share Posted October 31, 2008 Maybe something like this: SELECT * FROM table ORDER BY timestamp DESC LIMIT 1; Link to comment https://forums.phpfreaks.com/topic/130938-newest-date/#findComment-679717 Share on other sites More sharing options...
The Little Guy Posted October 31, 2008 Author Share Posted October 31, 2008 I don't think that is what I am after, because I don't really want to order by the timestamp, I just want to select the largest one... Link to comment https://forums.phpfreaks.com/topic/130938-newest-date/#findComment-679719 Share on other sites More sharing options...
Flames Posted October 31, 2008 Share Posted October 31, 2008 you cant have a largest timestamp, and its not ordering all the timestamps, its showing them all from your table ordering by timestamp descending (e.g. latest date first) then limiting itself to only get 1 result so it only ever gets 1 result as you put it the largest one. Link to comment https://forums.phpfreaks.com/topic/130938-newest-date/#findComment-679734 Share on other sites More sharing options...
The Little Guy Posted October 31, 2008 Author Share Posted October 31, 2008 Then how would yo integrade it into this query? It needs to be the newest from either the table: topics or replies. SELECT c.`category`, c.id AS 'cid', b.order, b.`title` AS 'title', b.`description` AS 'description', b.id 'bid', COUNT(t.id) AS 'topicCount', COUNT(r.id) AS 'postCount' FROM `boards` b LEFT JOIN topics t ON(t.`boardID` = b.id) LEFT JOIN replies r ON(r.`topic_id` = t.id) LEFT JOIN categories c ON(b.`group` = c.id) GROUP BY b.`id` ORDER BY c.`order` ASC, b.`group` ASC, c.`order` ASC Link to comment https://forums.phpfreaks.com/topic/130938-newest-date/#findComment-679738 Share on other sites More sharing options...
xtopolis Posted November 1, 2008 Share Posted November 1, 2008 TLG, what are you trying to accomplish in relation to the forum you're building? Is this to pull the title of the last post to display in the "Last post info" area? Also, it would help me if you gave me a data dump (not the schema, but actual data export as well) and tell me what columns to display and what conditions). .. mainly data to work with. I have a feeling it will be an additional column in that query that's something like: select max(post_date) from replies where topic id in topic.id or w/e Link to comment https://forums.phpfreaks.com/topic/130938-newest-date/#findComment-679769 Share on other sites More sharing options...
The Little Guy Posted November 1, 2008 Author Share Posted November 1, 2008 I just want to grab what the last post date of the item is, such as in the forum... "on Today at 12:24:24 PM" That shows the date and time of the last post or topic. I need to grab that date. I have tried a MAX, and it just displays as "1"... maybe I was doing it wrong. Link to comment https://forums.phpfreaks.com/topic/130938-newest-date/#findComment-679775 Share on other sites More sharing options...
xtopolis Posted November 1, 2008 Share Posted November 1, 2008 In a straight single column query, it returns the appropriate timestamp as you showed in the top post. Give us some sql of your structure + data if you can't get it. Link to comment https://forums.phpfreaks.com/topic/130938-newest-date/#findComment-679798 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.