Query on Arrse time

Discussion in 'ARRSE: Site Issues' started by Myss, May 14, 2006.

Welcome to the Army Rumour Service, ARRSE

The UK's largest and busiest UNofficial military website.

The heart of the site is the forum area, including:

  1. I am finding a hour's difference between the time shown on the 'Site Index' to the time on the board. I just clicked on one post which said 19.20 on the site index and when I read the post, it said 20.20, why's that?! Or is it just me? :?
  2. No it's not just you. I've noticed it too.

    Ice :?
  3. Thanks E. But nope, I'd done that already, check timezone and its still a hour's difference.

    Edited to add: Just looked on Site Index and its says this post is 16.35 and on here it s says 17.35 :roll: :?
  4. Oh yeah, you have a point - now I really do look like a twat.
  5. Bad CO

    Bad CO LE Admin Reviews Editor Gallery Guru

    You need to make sure that your time settings are correct:

    1. In the top time box (Timezone) you want the time set to GMT
    2. In the bottom time box (Daylight Saving) you want it set to Western Europe.

    It would also appear that the front page (Site Index) doesn't take any notice of BST so there is clearly a fault with that module. I'll look into it.
  6. Thanks BCO. Done and done - no change though, but from what you said about the front page does explain the problem is happening.
  7. Bad CO

    Bad CO LE Admin Reviews Editor Gallery Guru

    To solve it needs someone (GCO ......) to write a clever SQL query. Unfortunately its not a priority but its definitely on the to do list.
  8. Has there been any advance on this......
  9. Bad CO

    Bad CO LE Admin Reviews Editor Gallery Guru

    No, but you're welcome to have a go at rewriting this SQL query:
    $result = $db->sql_query("SELECT
     t.topic_id, t.topic_last_post_id, t.topic_title, t.topic_views, t.topic_replies,
     u.username, u.user_id,
     a.username AS authorname, a.user_id AS authorid,
     p.poster_id, FROM_UNIXTIME(p.post_time,'%m/%d/%Y @ %H:%i') as post_time
     FROM ".$prefix."_bbtopics t, ".$prefix."_bbforums f
     LEFT JOIN ".$prefix."_bbposts p ON (p.post_id = t.topic_last_post_id)
     LEFT JOIN ".$prefix."_users u ON (u.user_id = p.poster_id)
     LEFT JOIN ".$prefix."_users a ON (a.user_id = t.topic_poster)
     WHERE t.forum_id=f.forum_id $view
     ORDER BY t.topic_last_post_id DESC
     LIMIT 0,10", false, __FILE__, '');
    When you're done then let us know ........
  10. Auld-Yin

    Auld-Yin LE Reviewer Book Reviewer Reviews Editor

    :D :roll:
  11. Good CO

    Good CO LE Admin

    I did have a dig into this yesterday by comparing the scary code that creates the times in here with the scary code that creates the times on the front page. After about half an hour of deciphering, it got put in the pending tray again. It's quite complex as the site needs to consider whether someone is logged in and has set their own time, or a visitor. Also time is always a pain in computers as they just don't like things that don't work in nice round numbers. It's one of those things that I'll crack when I get a moment of enthusiasm, but I've got some other comparatively more important jobs to do first. Also, as it's in the software support forum, I'm hoping someone else might cure it and tell me the answer!
  12. I wasn't having a whinge BCO, I was merely enquiring into whether or not anything had been done. No offence intended.

  13. sysdate+1/24

    Would using the 'sysdate+1/24' idea from oracle work?

    FROM_UNIXTIME(p.post_time +1/24,'%m/%d/%Y @ %H:%i') ??
  14. Bad CO

    Bad CO LE Admin Reviews Editor Gallery Guru

    Nope - see GCOs point about the site allowing users to set their own time zones .....