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

Easy Solution Web : MySQL Tutorials

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