Solved

Pull dated data from database stored in epoch

Posted on 2013-11-07
9
384 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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 110

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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
WordPress  Failed to Import Media 8 67
What is the best SEO Web Analytics tool/program 3 60
How to display get_the_terms within a function 3 39
How can I split a variable 19 46
Although a lot of people devote their energy toward marketing for specific industries, there are some basic principles that can be applied to any sector imaginable. We’ll look at four steps to take and examine how those steps were put into action fo…
CTAs encourage people to do something specific to show interest in your company, product or service. Keep reading to learn why CTAs should always be thought of as extremely important, albeit small, sections of websites.
Any person in technology especially those working for big companies should at least know about the basics of web accessibility. Believe it or not there are even laws in place that require businesses to provide such means for the disabled and aging p…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

752 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