• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 443
  • Last Modified:

MySQL working with epoch

I have a database that is using epoch as a date completed field (d_completed).

I would like to pull information from this database by the items completed date.  I can not figure out how to run a query to do so.

What I need to do is pull all the completed items from the last week. For example I would run this query on a monday to fnd all the completed items from last week.

Here is what my test database looks like:



mysql> select * from 2do_todolist;
+----+--------------------------------------+---------+------------+-------------+------------+-------+----------------------------------------------------------------------+------+------+----+------+----------+------------+
| id | uuid                                 | list_id | d_created  | d_completed | d_edited   | compl | title                                                                | note | prio | ow | tags | tags_ids | duedate    |
+----+--------------------------------------+---------+------------+-------------+------------+-------+----------------------------------------------------------------------+------+------+----+------+----------+------------+
|  1 | af7eed54-f055-4e92-ae97-35c03c7c83c3 |       1 | 1319030944 |           0 | 1319030944 |     0 | Check NTP on ADIM                                                    | NULL |    0 |  1 |      |          | NULL       |
|  2 | c57a5620-ab25-4705-a55c-814056c357f2 |       1 | 1319030957 |           0 | 1319030957 |     0 | Create service account for ADIM scaENS                                | NULL |    0 |  2 |      |          | NULL       |
|  3 | 4a96df98-5640-4af2-85ac-090b6ef787bd |       2 | 1319032632 |  1319032861 | 1319032861 |     1 | Update Jordin to 2.8.6.1                                              | NULL |    0 |  1 |      |          | NULL       |
|  4 | b3f025c8-d4ff-4598-b980-770de4ad1a30 |       2 | 1319032641 |           0 | 1319032856 |     0 | Update Silverone to 2.8.6.1                                          | NULL |    0 |  8 |      |          | NULL       |
|  5 | 2db09dd2-5f5c-4416-9d98-cfc4aa987c45 |       2 | 1319032658 |  1319032863 | 1319032863 |     1 | Update Staplesone to 2.8.6.1                                          | NULL |    0 |  2 |      |          | NULL       |
|  6 | d6051450-fc1e-49e4-a5a4-1da83346da0e |       2 | 1319032671 |           0 | 1319032671 |     0 | Update Collector (Collector) to 2.8.6.1                               | NULL |    0 |  4 |      |          | NULL       |
|  7 | c16bf49c-0704-4827-bc7f-0d292dafdf72 |       2 | 1319032699 |  1319032867 | 1319032867 |     1 | Update Oinkmaster script to download rules on Jordin                  | NULL |    0 |  4 |      |          | NULL       |
|  8 | 32a92fd8-4edf-47ce-aa31-986c7d174121 |       2 | 1319032702 |           0 | 1319032702 |     0 | Update Oinkmaster script to download rules on Silverone              | NULL |    0 |  6 |      |          | NULL       |
|  9 | fdf47258-5ce1-4752-8433-3f1f790d3b09 |       2 | 1319032705 |  1319032866 | 1319032866 |     1 | Update Oinkmaster script to download rules on Staplesone              | NULL |    0 |  3 |      |          | NULL       |
| 10 | b81751ad-fe28-46df-b4d7-57adc4bb32aa |       1 | 1319034754 |           0 | 1319034754 |     0 | Archive old logs on original Jordin (16.50) and decommission machine |      |    0 |  3 |      |          | NULL       |
| 11 | 2db44aad-92c8-481e-845a-904c038212f6 |       3 | 1319034811 |           0 | 1319034893 |     0 | ENS1 needs to be rebuilt (Waiting for hardware)                     |      |    0 |  1 |      |          | 2012-02-17 |
| 12 | b4c7bef2-a61f-458e-9e2e-794aa0bbf42c |       3 | 1319034866 |           0 | 1319034866 |     0 | ENS1 (External Name Server) must be upgraded to RHEL6                 |      |    0 |  2 |      |          | 2012-02-17 |
| 13 | cac8b358-6b7c-4086-93cd-f51a6b86befc |       3 | 1319034879 |           0 | 1319034879 |     0 | ENS2 (External Name Server) must be upgraded to RHEL6                 |      |    0 |  3 |      |          | 2012-02-17 |
+----+--------------------------------------+---------+------------+-------------+------------+-------+----------------------------------------------------------------------+------+------+----+------+----------+------------+
13 rows in set (0.00 sec)
0
savone
Asked:
savone
  • 5
  • 3
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
You want the FROM_UNIXTIME() function I believe.
e.g., SELECT FROM_UNIXTIME(1319030944);
0
 
Kevin CrossChief Technology OfficerCommented:
0
 
savoneAuthor Commented:
That answers a very small portion of my question.  I need to know how to get everything from the last full week in a completed mysql select statement.

0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
savoneAuthor Commented:
After playing around with FROM_UNIXTIME it doesn't look like it is working correctly:


mysql> select title, FROM_UNIXTIME(d_completed) from 2do_todolist;
+----------------------------------------------------------------------+----------------------------+
| title                                                                | FROM_UNIXTIME(d_completed) |
+----------------------------------------------------------------------+----------------------------+
| Create service account for ADDM scans                                | 1969-12-31 19:00:00        |
| Update Moran to 2.8.6.1                                              | 2011-10-19 10:01:01        |
| Update Milverton to 2.8.6.1                                          | 1969-12-31 19:00:00        |
| Update Stapleton to 2.8.6.1                                          | 2011-10-19 10:01:03        |
| Update Moriarty (Collector) to 2.8.6.1                               | 1969-12-31 19:00:00        |
| Update Oinkmaster script to download rules on Moran                  | 2011-10-19 10:01:07        |
| Update Oinkmaster script to download rules on Milverton              | 1969-12-31 19:00:00        |
| Update Oinkmaster script to download rules on Stapleton              | 2011-10-19 10:01:06        |
| Archive old logs on original Jeeves (16.50) and decommission machine | 1969-12-31 19:00:00        |
| Lurch needs to be rebuilt (Waiting for hardware)                     | 1969-12-31 19:00:00        |
| NS1 (External Name Server) must be upgraded to RHEL6                 | 1969-12-31 19:00:00        |
| NS2 (External Name Server) must be upgraded to RHEL6                 | 1969-12-31 19:00:00        |
| Upgrade ADDM to verson 8.3.00                                        | 1969-12-31 19:00:00        |
| Check time sync on ADDM                                              | 1969-12-31 19:00:00        |
+----------------------------------------------------------------------+----------------------------+
14 rows in set (0.00 sec)
0
 
Kevin CrossChief Technology OfficerCommented:
Sorry, I figured once you knew how to convert the unix timestamp to a date, then you would be fine. Here is the rest.

WHERE FROM_UNIXTIME(d_completed) >= DATE_ADD(CURDATE(), INTERVAL -7 DAY)

If you need to exclude items from today, then add an AND.

AND FROM_UNIXTIME(d_completed) < CURDATE()

That should be what you need to get your data.
0
 
Kevin CrossChief Technology OfficerCommented:
Are you sure you are using UNIX timestamp, which is based on seconds since the unix Epoch 1970-01-01?
0
 
savoneAuthor Commented:
Yes, I am using epoch, I figured out that the items NOT marked completed have a null field for d_completed which is why they are returning 1969.

Thanks for your help!
0
 
Kevin CrossChief Technology OfficerCommented:
I see now. You are getting "1969-12-31 19:00:00" -- usually NULL returns NULL, but 0 is 1970-01-01 00:00:00. You must be adjusting in GMT-5 (Eastern) like me, so -5 hours is 7p.m. the day before. Nice catch.

Best regards and happy coding,

Kevin
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now