Why I Love The ISO Date Format


After working with different types of data for many years you gain appreciation for a few things. One of the odd things I've gain favor of is the ISO Date Format (YYYYMMDD). This simple concatenation of a four digit year (YYYY), 2 digit month (MM) and 2 digit day (DD) makes it very easy to query and work with. Not to mention this format is internationally recognized and compatible with gregorian and julian calendars. With or without built in programing/scripting language functions I can break this date format down into easy to use parts. Let me show you a few PHP snippets I've used in the past.

Example: Parsing ISO Date Format Using date() and strtotime() Functions $str_date = 20131231;
//strtotime() will convert it to Unix Time, date() returns part requested
$year = date("Y",strtotime($str_date)); //2013 - "Y"
$month = date("m"),strtotime($str_date)); //12 - "m"
$day = date("d"),strtotime($str_date)); //31 - "d"
echo $year.$month.$day; //20131231

Example: Parsing ISO Date Format Using substr() Function $str_date = 20131231;
//even without the use of date() and strtotime() functions, I can parse this date format into year, month, day using the substr() function
$year = substr($str_date,0,4); //2013
$month = substr($str_date,4,2); //12
$day = substr($str_date,6,2); //31
echo $year.$month.$day; //20131231

Where I think this date format really shines is in SQL queries. Because of the nature of the date format being an integer it makes it easy to find all the dates between a range of dates. For example say if I wanted all records where the date is between January 1st 2013 and October 21st 2013. My query would look something like the example below.

SELECT * FROM records WHERE mydate BETWEEN 20130101 AND 20131021;

What if I needed a quick and dirty way of querying for all records during one month (April 2013) or even a full year (2013). My queries could look something like the examples below.

SELECT * FROM records WHERE mydate LIKE '201304%'; #Full Year-Month
SELECT * FROM records WHERE mydate LIKE '2013%'; #Full Year

One last thing about this date format. How can we convert to it? Well Below are some examples in different languages. I hope this may have inspired you to take a look at this date format. I know it makes my life easier as a developer at times. In a future article I'll talk about the benefits of using Unix timestamps as well.

#php (UNIX TIMESTAMP to ISO): date("Ymd"),strtotime("yourdatehere"));
#mysql (MYSQL Datetime to ISO): date_format(date(NOW(),'%Y%m%d'))
#javascript (JS Date to ISO): new Date().toISOString().slice(0,10).replace(/-/g,"")

#oracle (ISO to Oracle Date): to_date('20170101','yyyymmdd')
#mssql (ISO to MSSQL Date): CAST(CAST('20170101' AS varchar) AS datetime)

Sources: w3.org
  0

Categories: Snippets

Tags: development, mysql, php

Starting Over



I've decided enough is enough. I'm tired of WordPress. I've decided to develop my own blog system. I've named my project SmallBlog. Right now it's basic. But you're looking at it and it works! It has a lot of the features I enjoyed about WordPress without all the issues. I haven't decided if I'm going to release my code open source or not. One of the things I don't like about WordPress is the fact everyone knows it's vulnerabilities. Because it is so popular it becomes a target of exploit. More to come!
  0

Categories: News

Tags: development, News