Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Pull dated data from database stored in epoch

Posted on 2013-11-07
9
Medium Priority
?
392 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
[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
  • 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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 2000 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 111

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

Cloud Training Guides

FREE GUIDES: In-depth and hand-crafted Linux, AWS, OpenStack, DevOps, Azure, and Cloud training guides created by Linux Academy instructors and the community.

Question has a verified solution.

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

In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
Without even knowing it, most of us are using web applications on a daily basis.  In fact, Gmail and Yahoo email, Twitter, Facebook, and eBay are used by most of us daily—and they are web applications. We generally confuse these web applications to…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

715 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