Solved

Pull dated data from database stored in epoch

Posted on 2013-11-07
9
379 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
PHP and JQuery Syntax question 4 26
CLI command keep running after close 7 36
Where on a calculated field 1 20
Php logic to add to date card 9 18
Developer portfolios can be a bit of an enigma—how do you present yourself to employers without burying them in lines of code?  A modern portfolio is more than just work samples, it’s also a statement of how you work.
Color can increase conversions, create feelings of warmth or even incite people to get behind a cause. If you want your website to really impact site visitors, then it is vital to consider the impact color has on them.
The viewer will learn how to count occurrences of each item in an array.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

840 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