Solved

Simple query using a timestamp field and a date range

Posted on 2011-03-10
6
505 Views
Last Modified: 2012-05-11
New to mySQL.  This is on a Linux box running MySQL 5.1.52

How do I do a simple query to retrieve e a date range from a timestamp field?.

The field is named  time  .  I know, but I didn't name it. This is what I need to do iinteractively.

Select * from journal
WHERE time > 2009/12/31 AND
time < 2010/12/31

I also need to do the same in a form with two php form fields, startdate and endate, formatted as yyyy-mm-dd

Select * from diaries
WHERE time >startdate AND
time < enddate

Help please.
0
Comment
Question by:Waterstone
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 33

Expert Comment

by:jppinto
ID: 35097047
Select * from journal
WHERE time > #2009/12/31# AND
time < #2010/12/31#

0
 

Author Comment

by:Waterstone
ID: 35097157
Thanks, but it does not work.
Does not like the # signs. Turns the line into a comment.
0
 

Author Comment

by:Waterstone
ID: 35097178
Sorry,w as not specific.  I'm trying to run the query in mySQL, not a php page.  That must be the php code.

I'll try that in pfp after I verify that the data is there using an interactive query using Navicat or MySQL Workbench.
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 78

Accepted Solution

by:
arnold earned 500 total points
ID: 35097302
Is the time column uses  timestamp format (int(10)) OR is it in date format?
could you post the show create table journal

the query tool in workbench and run
select * from journal where time >'2009/12/31' and time <= 'the end_dateof_interest'
0
 

Author Comment

by:Waterstone
ID: 35097539
Thanks, that worked. I was playing with date_format parameters and looking for a more complex issue. Field is named time, type is timestamp.
0
 
LVL 78

Expert Comment

by:arnold
ID: 35097796
For future use, I'd suggest that you use unix_timestamp (int (12)) as the definition of the column which represents the number of seconds since january 1st 1970 GMT
IT simplifies queries such that you do not need to use date_add or similar functions to manipulate the date.
i.e. adding or subtracting 3600 from the column, will result in an hour shift eiher way.etc.
When displaying the unix_timestamp can then be converted for date display and provides for better customization where the Timezone of the client needs to be taken into account. i.e. one is in the EST, GMT, Australian etc. and the interface can simply grab the timestamp and during the display convert it into the date with the right timezone.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question