unix_timestamp(), from_unixtime(), date_add(), date_sub() and interval concept in MySQL

Epoch time, Unixtime, Unix timestamp, all these are same. It is basically no. of seconds passed from 1 january 1970 00 hrs 00 min 00 sec which might cause problems on January 19, 2038. This is short description about Epoch time.

 

Epoch time for a date having 0 hrs 0 min 0 sec in MySQL using unix_timestamp()

mySQL -> select unix_timestamp(“2017-12-07”);

1512585000

Epoch time for a date having 23 hrs 59 min 59 sec in MySQL using unix_timestamp()

mySQL -> select unix_timestamp(“2017-12-07 23:59:59”);

1512671399

Readable date from unix_timestamp() function in MySQL

mySQL -> select from_unixtime(1512671399);

2017-12-07 23:59:59

Find Epoch time of  after 30 days in MySQL using unix_timestamp() and date_add()

mySQL -> select unix_timestamp(date_add(NOW(), INTERVAL 30 day));

Now here is string in ‘%Y-%m-%d %h:%i:%s’ format. Now you can play with it on the basis of your need. As a developer, you will need a concept to apply it on a broad level.

like  select unix_timestamp(date_add(“2017-12-05”, INTERVAL 30 day));

It will give us Epoch time of 04-01-2018 00:00:00 And if you want to get 04-01-2018 23:59:59

like  select unix_timestamp(date_add(“2017-12-05 23:59:59”, INTERVAL 30 day));

Find This mySQL -> select unix_timestamp(date_sub(NOW(), INTERVAL 30 day));

Here date in string format is given example, here it can we valid date it will work like a charm.

Now let’s talk about the optimization:-

In Database searching process, to search for the number is way faster than searching for the string because in searching we mostly do comparison and Integer comparison is faster than String.

So storing a date in DB is always good as integer format instead of a string.

So always store a date in integer format. It will help you to optimize your searching in DB when you will have millions of records.

But there is a drawback which I mentioned above that it will work till 19 Jan 2038. after that, it will start creating problem.

For many other details :-Date Function

 

Date related functions of MySQL

A developer has to deal with these below mentioned date related function of MySQL. So let’s understand these function in details.

List of MySQL Date function

  • NOW(): It will give you the current date and time.

For example:

MySQL > select NOW();
      > 2017-02-25 00:52:57;
  • CURDATE(): It will give you current date.

For example :

MySQL > select CURDATE();
> 2017-02-25;
  • UNIX_TIMESTAMP() : It will give you the current date and time in epoch format

For example:

MySQL > select UNIX_TIMESTAMP();
> 1487964177
MySQL > select UNIX_TIMESTAMP('2017-02-25 00:52:57');
> 1487964177
  • FROM_UNIXTIME(): It will give you DATE and TIME from the epoch time format.

For example:

MySQL > SELECT FROM_UNIXTIME(1447430881);
> '2015-11-13 10:08:01'
MySQL > SELECT FROM_UNIXTIME(1447430881) + 0;
> 20151113100801
  • DATE_FORMAT() : It will let you decide in what format you want to show date.

For example :

MySQL > select DATE_FORMAT(NOW(),'%m-%d-%Y')
> 11-04-2014
MySQL > DATE_FORMAT(NOW(),’%b %d %Y %h:%i %p’)
>Nov 04 2014 11:45 PM

Format you can use :

%a  Abbreviated weekday name (Sun-Sat)
%b  Abbreviated month name (Jan-Dec)
%c  Month, numeric (0-12)
%D  Day of the month with English suffix (0th, 1st, 2nd, 3rd …..)
%d  Day of month, numeric (00-31)
%e  Day of month, numeric (0-31)
%f  Microseconds (000000-999999)
%H  Hour (00-23)
%h  Hour (01-12)
%I  Hour (01-12)
%i  Minutes, numeric (00-59)
%j  Day of year (001-366)
%k  Hour (0-23)
%l    Hour (1-12)
%M  Month name (January-December)
%m  Month, numeric (00-12)
%p  AM or PM
%r  Time, 12-hour (hh:mm:ss followed by AM or PM)
%S  Seconds (00-59)
%s  Seconds (00-59)
%T  Time, 24-hour (hh:mm:ss)
%U  Week (00-53) where Sunday is the first day of week
%u  Week (00-53) where Monday is the first day of week
%V  Week (01-53) where Sunday is the first day of week, used with %X
%v  Week (01-53) where Monday is the first day of week, used with %x
%W  Weekday name (Sunday-Saturday)
%w  Day of the week (0=Sunday, 6=Saturday)
%X  Year for the week where Sunday is the first day of week, four digits, used with %V
%x  Year for the week where Monday is the first day of week, four digits, used with %v
%Y    Year, numeric, four digits
%y    Year, numeric, two digits