Solved

Pull dated data from database stored in epoch

Posted on 2013-11-07
9
375 Views
Last Modified: 2013-11-08
I have a database that stores information on tasks.  There is a d_completed field that saves the date a task was completed in epoch.

I would like to query the database for date ranges.  For example, pull all tasks down between 11-02-2013 and 11-07-2013.

Can someone help me with the syntax?
0
Comment
Question by:savone
  • 4
  • 4
9 Comments
 
LVL 5

Expert Comment

by:dannygonzalez09
ID: 39632069
this should get you the output

SELECT * FROM MyTable
WHERE d_completed BETWEEN UNIX_TIMESTAMP('11-02-2013') AND UNIX_TIMESTAMP('11-07-2013')
0
 
LVL 23

Author Comment

by:savone
ID: 39632163
select * from 2do_list where d_completed between UNIX_TIMESTAMP('11-02-2013') AND UNIX_TIMESTAMP('11-07-2013');
Empty set, 2 warnings (0.00 sec)
0
 
LVL 5

Expert Comment

by:dannygonzalez09
ID: 39632186
What warnings are you getting ?
 can you post some sample epoch times from your table
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 23

Author Comment

by:savone
ID: 39632213
It didn't say which warnings, I post the output as I saw it.

Here is an example of the epoch time:
1383863910
0
 
LVL 5

Expert Comment

by:dannygonzalez09
ID: 39632229
I don't see any problems with the date functions..try this

i'm converting the epoch value and then datetime to the epoch time

SELECT FROM_UNIXTIME(1383863910), UNIX_TIMESTAMP('2013-11-07 22:38:30')

ah it might be the format try

select * from 2do_list where d_completed between UNIX_TIMESTAMP('2013-11-02') AND UNIX_TIMESTAMP('2013-11-07');
0
 
LVL 23

Author Comment

by:savone
ID: 39632238
select * from 2do_list where d_completed between UNIX_TIMESTAMP('2013-11-02') AND UNIX_TIMESTAMP('2013-11-07');
Empty set (0.00 sec)

I only have 3 entries in the database and they are all from today, so I would assume it should return 3 rows, correct?
0
 
LVL 5

Accepted Solution

by:
dannygonzalez09 earned 500 total points
ID: 39632241
its basically getting all the data from 2nd midnight upto 7th midnight

change the date to '2013-11-08'
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 39633257
Make a quick search of EE articles for "Beware of Between" (or similar) to see the risks inherent in the way MySQL handles the BETWEEN clause when using DATETIME columns.

This article shows some of the ways to handle DATETIME information in PHP and SQL.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html
0
 
LVL 23

Author Closing Comment

by:savone
ID: 39633434
Thanks for the help!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Learn by example how to specify CSS selectors for Selenium WebDriver test automation software.
This article discusses four methods for overlaying images in a container on a web page
The viewer will learn how to count occurrences of each item in an array.
The viewer will get a basic understanding of what section 508 compliance can entail, learn about skip navigation links, alt text, transcripts, and font size controls.

776 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