Solved

Pull dated data from database stored in epoch

Posted on 2013-11-07
9
370 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
 
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
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 108

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This video teaches users how to migrate an existing Wordpress website to a new domain.
The viewer will learn how to dynamically set the form action using jQuery.

762 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now