{"id":2508,"date":"2015-01-12T15:34:18","date_gmt":"2015-01-12T11:34:18","guid":{"rendered":"http:\/\/nayarweb.com\/blog\/?p=2508"},"modified":"2017-04-16T21:59:08","modified_gmt":"2017-04-16T17:59:08","slug":"the-timestamp","status":"publish","type":"post","link":"https:\/\/nayarweb.com\/blog\/2015\/the-timestamp\/","title":{"rendered":"The Timestamp"},"content":{"rendered":"<p>No one stores <em>age<\/em> in a database. Age is a <em>derived<\/em> attribute. We usually store <em>date of birth<\/em> of someone. <\/p>\n<p>As a noob, I put the <em>dob<\/em> field as <code>INT(10)<\/code> in my database. To select e.g. users between 18 &#8211; 25 years old, I&#8217;d run the following SQL query:<\/p>\n<p><code>SELECT *<br \/>\nFROM users<br \/>\nWHERE dob <= unix_timestamp(NOW() <= (NOW() - INTERVAL 18 YEAR)\nAND dob >= unix_timestamp(NOW() - INTERVAL 25 YEAR)<\/code><\/p>\n<p>It worked fine until I realized that it would not work for people who are born before 1970. PHP&#8217;s strtotime() function was returning a negative number as timestamp which is correct but the DB would refuse to put it since it was INT. <\/p>\n<p>After some more googling, I came to know that I should have used DATE type. <\/p>\n<p>After that, everything gets solved.<\/p>\n<p><code>SELECT *<br \/>\nFROM users<br \/>\nWHERE dob <= (NOW() - INTERVAL 18 YEAR)\nAND dob >= (NOW() - INTERVAL 90 YEAR)<br \/>\n<\/code><\/p>\n<p>The advantage now is that it can be used to POST with HTML5 forms and used as value directly.<\/p>\n<pre><input type=\"date\" name = \"dob\" value=\"<?php echo $users->attributes['dob']; ?>\" \/><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>No one stores age in a database. Age is a derived attribute. We usually store date of birth of someone. As a noob, I put the dob field as INT(10) in my database. To select e.g. users between 18 &#8211; 25 years old, I&#8217;d run the following SQL query: SELECT * FROM users WHERE dob<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[210,1],"tags":[],"class_list":["post-2508","post","type-post","status-publish","format-standard","hentry","category-technology","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/nayarweb.com\/blog\/wp-json\/wp\/v2\/posts\/2508","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/nayarweb.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/nayarweb.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/nayarweb.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/nayarweb.com\/blog\/wp-json\/wp\/v2\/comments?post=2508"}],"version-history":[{"count":5,"href":"https:\/\/nayarweb.com\/blog\/wp-json\/wp\/v2\/posts\/2508\/revisions"}],"predecessor-version":[{"id":2513,"href":"https:\/\/nayarweb.com\/blog\/wp-json\/wp\/v2\/posts\/2508\/revisions\/2513"}],"wp:attachment":[{"href":"https:\/\/nayarweb.com\/blog\/wp-json\/wp\/v2\/media?parent=2508"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/nayarweb.com\/blog\/wp-json\/wp\/v2\/categories?post=2508"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/nayarweb.com\/blog\/wp-json\/wp\/v2\/tags?post=2508"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}