• 0

[SQL] Select timestamps over 1 hour old


Question

Hello,

In my table I've got a column called 'time_handled' and in there is a MySQL timestamp. On another page I want to run a query that brings up records where the time_handled is over 1 hour old.

So far I've tried this:

SELECT * FROM table WHERE time_handled < NOW() - 3600

This worked for a bit but as soon as the clock changed from 16:59 to 17:00 the server started showing records less than 1 hour old again. I think it's something to do with how MySQL treats NOW() when used as an integer.

Does anyone out there know the correct way to do this?

Thanks,

Simsie

7 answers to this question

Recommended Posts

  • 0

By timestamp, do you mean a datetime field (YYYY-MM-DD HH:MM:SS) or is it an INT that you store the UNIX timestamp in directly?

If it's the later, you will need to convert NOW() into a UNIX timestamp for it to work, i.e.:

SELECT * FROM table WHERE time_handled < UNIX_TIMESTAMP(NOW()) - 3600;

If the column is a datetime or date column and you have MySQL 5.0 you can do:

SELECT * FROM table WHERE TIMESTAMPDIFF(MINUTE, time_handled, NOW()) > 60;

Edited by DecoyDuck
  • 0
  F7S said:
Ah, sorry, my solution was for MSSQL :) You didn't make it clear you were using MySQL in your original post.
  simsie said:
Hello,

In my table I've got a column called 'time_handled' and in there is a MySQL timestamp...

:p

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.
  • Posts

    • LibreOffice narrows gap with Microsoft Office in 25.8 Beta 1 by David Uzondu The Document Foundation has released LibreOffice 25.8 Beta 1 for public testing on Linux, macOS, and Windows. This is the second pre-release for the 25.8 cycle and the foundation says that the final, stable version of LibreOffice 25.8 is expected to land at the end of August 2025. Starting off with Writer, LibreOffice's Word, the developers have finally addressed some long-standing annoyances, including a new command to easily insert a paragraph break right before a table. This beta also introduces a useful privacy feature in its Auto-Redact tool, letting you strip all images from a document with a single option. To use it, go to Tools and select the Auto-Redact option: The application has improved its ability to handle different languages for punctuation, preventing mix-ups in multilingual documents. Other notable improvements have also been made. A new hyphenation rule lets you choose to prevent a word from splitting at the end of a page, moving the whole line to the next page instead. Microsoft Word has had this feature for years now. The Navigator now displays a handy tooltip with word and character counts for headings and their sub-outlines. Scrolling behavior when selecting text has been improved, making it less erratic. A new command with a keyboard shortcut was added for converting fields into plain text. Calc gets a lot of new functions that bring it closer to its competitors like Excel, including TEXTSPLIT, VSTACK, and WRAPROWS. Impress now properly supports embedded fonts in PPTX files, which should reduce headaches when sharing presentations with PowerPoint users. Alongside these additions, the project is also cleaning house; support for Windows 7, 8, and 8.1 has been completely dropped. There are also smaller UI tweaks across the suite, like allowing a single click to enter rotation mode for objects in Writer and Calc. macOS users get better integration, with proper support for native full screen mode and new window management features from the Sequoia update. In terms of performance, the team has optimized everything from loading huge DOC files and XLSX spreadsheets with tons of conditional formatting to simply switching between sheets in Calc. These improvements should be noticeable, especially when working with complex documents. A new application-wide "Viewer mode" has also been implemented, which opens all files in a read-only state for quick, safe viewing. On a related note, The Document Foundation has joined efforts by the likes of KDE to encourage Windows 10 users to switch to Linux. Also, you might have heard that Denmark, in a bid to lessen its reliance on Microsoft, has decided to make a full switch to LibreOffice, with plans to begin phasing out Office 365 in certain ministries as early as next month. If you're interested in this release, you can read the full release notes and download the binaries for your platform: Windows, macOS (Intel | Apple Silicon), or Linux (DEB | RPM). You can also get the latest stable version from our software stories page.
    • Until it can be used 100% offline (ie: PST file support or equiv) not even considering it. I'll jump to Thunderbird first which has gotten a LOT better since the last time I looked at it.
  • Recent Achievements

    • Explorer
      Case_f went up a rank
      Explorer
    • Conversation Starter
      Jamie Smith earned a badge
      Conversation Starter
    • First Post
      NeoToad777 earned a badge
      First Post
    • Week One Done
      JoeV earned a badge
      Week One Done
    • One Month Later
      VAT Services in UAE earned a badge
      One Month Later
  • Popular Contributors

    1. 1
      +primortal
      547
    2. 2
      ATLien_0
      230
    3. 3
      +FloatingFatMan
      158
    4. 4
      Michael Scrip
      114
    5. 5
      +Edouard
      111
  • Tell a friend

    Love Neowin? Tell a friend!