• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 427
  • 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
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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