Using Unix Timestamps in MySQL

This page gives you information on how to easily use Unix Timestamps in MySQL.

Quick summary

Goal	MySQL query
Get current epoch time	SELECT UNIX_TIMESTAMP(NOW()) (now() is optional)
Today midnight	SELECT UNIX_TIMESTAMP(CURDATE())
Yesterday midnight	SELECT UNIX_TIMESTAMP(DATE_ADD(CURDATE(),INTERVAL -1 DAY))
Jan 1 of current year	SELECT UNIX_TIMESTAMP(CONCAT(YEAR(CURDATE()),'-01-01'))
Convert from date to epoch	SELECT UNIX_TIMESTAMP(timestring) Time format: YYYY-MM-DD HH:MM:SS or YYMMDD or YYYYMMDD
Convert from epoch to date	SELECT FROM_UNIXTIME(epoch timestamp, optional output format) The default output is YYYY-MM-DD HH:MM:SS FROM_UNIXTIME doesn't work with negative timestamps

The Mini-Course

Let's first create a simple logging-table and some sample records.
All queries on this page will work with the following table.

-- Table "mytable" DDL

CREATE TABLE `mytable` (
  `userId` int(11) NOT NULL,
  `url` varchar(100) NOT NULL,
  `epoch` int(11) NOT NULL
);

INSERT INTO mytable VALUES(1,'homepage',1225540800);
-- User 1 visited the url homepage on Nov 1, 2008
INSERT INTO mytable VALUES(2,'contact',1225886400);
-- User 2 visited the url contact on Nov 5, 2008
INSERT INTO mytable VALUES(3,'contact',1225972800);
-- User 3 visited the url contact on Nov 6, 2008
INSERT INTO mytable VALUES(4,'homepage',1228132800);
-- User 4 visited the url homepage on Dec 1, 2008

Converting to readable dates

SELECT userId, url, FROM_UNIXTIME(epoch) FROM mytable

This query outputs:

1   homepage   2008-11-01 13:00:00
2   contact    2008-11-05 13:00:00
3   contact    2008-11-06 13:00:00
4   homepage   2008-12-01 13:00:00

You can format your date by using specifiers (look below for a list of specifiers).

SELECT userId, url, FROM_UNIXTIME(epoch,"%Y-%m-%d") FROM mytable

Output:

1   homepage   2008-11-01
2   contact    2008-11-05
3   contact    2008-11-06
4   homepage   2008-12-01

Grouping Epochs

Let's say you want to get statistics by month. This query groups months, and counts the number of visitors (records) in each month. We order by epoch to get all results in the right order.

SELECT DISTINCT FROM_UNIXTIME(epoch,"%M, %Y") AS month, count(*) as numberOfVisits
FROM mytable
GROUP BY month
ORDER BY epoch

This outputs:

November, 2008   3
December, 2008   1

This query can be easily changed to get statistics per year, per day, per day of the week, per hour of the day, etc. For example, to get yearly stats change the query to:

SELECT DISTINCT FROM_UNIXTIME(epoch,"%Y") AS year, count(*) as numberOfVisits
FROM mytable
GROUP BY year
ORDER BY epoch

Adding a new record to our database

Use the UNIX_TIMESTAMP() function to convert MySQL dates/times (such as now() = current time) to epochs.

INSERT INTO mytable VALUES(1,'pagename',UNIX_TIMESTAMP(now()))

or use YYYY-MM-DD HH:MM:SS :

INSERT INTO mytable VALUES(1,'pagename',UNIX_TIMESTAMP('2008-12-01 12:00:00'))

Negative Epochs

There's one big problem with MySQL: MySQL cannot convert negative epoch timestamps (dates before 1-1-1970). This creates problems with for example birthdates. But there are workarounds.

When converting from epoch to human-readable date use the DATE_ADD function:

-- converting to MySQL date:
SELECT DATE_ADD(FROM_UNIXTIME(0), interval -315619200 second);
-- converting your epoch to a date string:
SELECT DATE_FORMAT(DATE_ADD(FROM_UNIXTIME(0), interval -315619200 second),'%Y-%m-%d');

Where -315619200 is your negative epoch. This query returns: 1960-01-01 01:00:00

When converting normal dates to epoch use TIMESTAMPDIFF:

SELECT TIMESTAMPDIFF(second,FROM_UNIXTIME(0),'1960-01-01 00:00:00' );

Replace the 1960 date with your date in your local timezone (MySQL time_zone).

MySQL date format specifiers

Specifier	Description
%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 the month, numeric (00..31)
%e	Day of the 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 the week
%u	Week (00..53), where Monday is the first day of the week
%V	Week (01..53), where Sunday is the first day of the week; used with %X
%v	Week (01..53), where Monday is the first day of the 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 the week, numeric, four digits; used with %V
%x	Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v
%Y	Year, numeric, four digits
%y	Year, numeric (two digits)
%%	A literal '%' character

MySQL 8.0 info in more detail

Using date_add in MySQL to add intervals to dates

Like other database management systems, MySQL has a range of date functions which allow you to change the formatting of dates, get day/week/month/etc parts of dates, and add or subtract intervals to a specified date. This post looks at how to add intervals to a date in MySQL.

The output from the examples in this post were executed from the MySQL Command Line, using \G to execute the SQL command. I've then simply copied and pasted the query result into this page.

The DATE_ADD() function and its synonym ADDDATE() allow you to add or subtract an interval to the selected date, date function or date constant. DATE_SUB() and SUBDATE() work in the same way but the interval specified is subtracted. (If the interval was negatvie DATE_SUB() makes it positive).

The easiest way to explain is with an example. The first example selects the current date and one month from the current date:

mysql> SELECT NOW(), DATE_ADD(NOW(), INTERVAL 1 MONTH) \G
*************************** 1. row ***************************
                            NOW(): 2008-09-25 11:43:29
DATE_ADD(NOW(), INTERVAL 1 MONTH): 2008-10-25 11:43:29
1 row in set (0.00 sec)

The second example is the same, but for one month ago:

mysql> SELECT NOW(), DATE_ADD(NOW(), INTERVAL -1 MONTH) \G
*************************** 1. row ***************************
                             NOW(): 2008-09-25 11:43:57
DATE_ADD(NOW(), INTERVAL -1 MONTH): 2008-08-25 11:43:57
1 row in set (0.00 sec)

It is also possible to do this without calling the function at all and simply using arithmetic to add the interval to the datetime like so:

mysql> SELECT NOW(), NOW() + INTERVAL 1 MONTH \G
*************************** 1. row ***************************
                   NOW(): 2008-09-25 11:46:53
NOW() + INTERVAL 1 MONTH: 2008-10-25 11:46:53
1 row in set (0.01 sec)

My examples above all use NOW() as the datetime to add the interval to but it can just as easily be a column from a database query. For example if we have a table called "products" and it has a column called "backorder_date" which has a column type of date, we could run this query to add three days onto the back order date which is the value we might display on a website:

mysql> SELECT DATE_ADD(backorder_date, INTERVAL 3 DAY) AS backorder_date 
    -> FROM products LIMIT 1 \G
*************************** 1. row ***************************
backorder_date: 2008-10-18
1 row in set (0.00 sec)

For a complete list of the interval types check out the DATE_ADD() function on the MySQL website manual page.

It's very easy to add and subtract dates using MySQL. The are often circumstances when you would need to use this and do it in the database rather than in business logic or website code, such as calculating the backorder date of a product in the last example above.

Related posts:

• Convert a UNIX timestamp to a datetime with MySQL
• Setting the MySQL timezone per connection
• Formatting Dates and Times with MySQL

The subquery does not return the expected results

This is the query I have written to get the plans chosen by an user. But this is returning the records in usersubscription table even if the user is not subscribed (if there is no records in the table corresponding to the user).

$userid=$_POST['userid'];
$videoid=$_POST['videoid'];
$subscribedquery=$this->db->query("select id from usersubscription where plan_id IN
        (SELECT DISTINCT plan_id FROM subscribed_videos sv where sv.videoid = $videoid)
        OR id IN (SELECT DISTINCT assosiated_plan_id
        FROM subscription_groups sg
        JOIN subscribed_videos sv ON sv.plan_id = sg.plan_id
        WHERE sv.videoid = $videoid) and user_id=$userid");

Below I am sharing the structure of all tables.

CREATE TABLE IF NOT EXISTS `subscribed_videos` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `plan_id` int(11) NOT NULL,
  `videoid` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=26 ;

INSERT INTO `subscribed_videos` (`id`, `plan_id`, `videoid`) VALUES
(7, 2, 1),
(8, 2, 2),
(14, 1, 3),
(15, 1, 4),
(16, 1, 5),
(17, 1, 21),
(18, 1, 28),
(19, 1, 2),
(20, 3, 4),
(21, 3, 6),
(24, 5, 25),
(25, 6, 5);

CREATE TABLE IF NOT EXISTS `subscription_groups` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `plan_id` int(11) NOT NULL,
  `assosiated_plan_id` int(11) NOT NULL,
  `added_on` int(11) NOT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO `subscription_groups` (`id`, `plan_id`, `assosiated_plan_id`, `added_on`) VALUES
(1, 1, 1, 0),
(2, 2, 2, 0),
(3, 3, 3, 0),
(4, 4, 1, 0),
(5, 4, 2, 0),
(6, 4, 3, 0),
(12, 5, 5, 0),
(13, 5, 1, 0),
(14, 5, 2, 0),
(15, 6, 1, 0);

CREATE TABLE IF NOT EXISTS `subscription_plans` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `plan` varchar(256) NOT NULL,
  `days_limit` int(11) NOT NULL,
  `added_on` int(11) NOT NULL,
  `status` int(11) NOT NULL,
  `rate` decimal(6,2) NOT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO `subscription_plans` (`id`, `plan`, `days_limit`, `added_on`, `status`, `rate`) VALUES
(1, 'PlanA', 15, 1398249706, 1, 150.00),
(2, 'PlanB', 15, 1398249679, 1, 100.00),
(3, 'PlanC', 15, 1398249747, 1, 100.00),
(4, 'PlanD', 10, 1398249771, 1, 500.00),
(5, 'PlanE', 15, 1398250104, 1, 200.00),
(6, 'Plan R1', 20, 1398250104, 1, 200.00);

CREATE TABLE IF NOT EXISTS `usersubscription` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `plan_id` int(11) NOT NULL,
  `subscribed_on` int(11) NOT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO `usersubscription` (`id`, `user_id`, `plan_id`, `subscribed_on`) VALUES
(1, 1, 1, 1399091458);

Content:

SELECT * FROM subscribed_videos;
+----+---------+---------+
| id | plan_id | videoid |
+----+---------+---------+
|  7 |       2 |       1 |
|  8 |       2 |       2 |
| 14 |       1 |       3 |
| 15 |       1 |       4 |
| 16 |       1 |       5 |
| 17 |       1 |      21 |
| 18 |       1 |      28 |
| 19 |       1 |       2 |
| 20 |       3 |       4 |
| 21 |       3 |       6 |
| 24 |       5 |      25 |
| 25 |       6 |       5 |
+----+---------+---------+

SELECT * FROM subscription_groups;
+----+---------+--------------------+----------+
| id | plan_id | assosiated_plan_id | added_on |
+----+---------+--------------------+----------+
|  1 |       1 |                  1 |        0 |
|  2 |       2 |                  2 |        0 |
|  3 |       3 |                  3 |        0 |
|  4 |       4 |                  1 |        0 |
|  5 |       4 |                  2 |        0 |
|  6 |       4 |                  3 |        0 |
| 12 |       5 |                  5 |        0 |
| 13 |       5 |                  1 |        0 |
| 14 |       5 |                  2 |        0 |
| 15 |       6 |                  1 |        0 |
+----+---------+--------------------+----------+

SELECT * FROM subscription_plans;
+----+---------+------------+------------+--------+--------+
| id | plan    | days_limit | added_on   | status | rate   |
+----+---------+------------+------------+--------+--------+
|  1 | PlanA   |         15 | 1398249706 |      1 | 150.00 |
|  2 | PlanB   |         15 | 1398249679 |      1 | 100.00 |
|  3 | PlanC   |         15 | 1398249747 |      1 | 100.00 |
|  4 | PlanD   |         10 | 1398249771 |      1 | 500.00 |
|  5 | PlanE   |         15 | 1398250104 |      1 | 200.00 |
|  6 | Plan R1 |         20 | 1398250104 |      1 | 200.00 |
+----+---------+------------+------------+--------+--------+

 SELECT * FROM usersubscription
+----+---------+---------+---------------+
| id | user_id | plan_id | subscribed_on |
+----+---------+---------+---------------+
|  1 |       1 |       1 |    1399091458 |
+----+---------+---------+---------------+

Fiddle of same: http://sqlfiddle.com/#!2/90b2e/1

I expect the result to be like this if the user is already subscribed to a plan of the selected video otherwise the query should return empty records:

id
---
1

Also how can I return the records only if the plan is not expired for the user using the query itself. ie, when an user purchases a plan, it will be entered in the usersubscription table. The subscribed_on field will contain the php unix time() in which it is purchased. So I would like to get only the plans corresponding to a user and a video, which is not expired, in this query. The expiry days is stored as days in days_limit field of subscription_plans table (eg: 15).

Can anyone help me to find an appropriate solution for this.

Thanks in advance.

---

I would say you should try this using joins

SELECT DISTINCT s.id ,
FROM_UNIXTIME(p.`added_on`),
DATE_ADD(FROM_UNIXTIME(s.`subscribed_on`), INTERVAL p.`days_limit` DAY) `expiry_date`
FROM usersubscription s
LEFT JOIN subscribed_videos v ON (s.plan_id = v.plan_id)
LEFT JOIN subscription_groups g ON(s.id = assosiated_plan_id )
LEFT JOIN subscribed_videos sv ON sv.plan_id = g.plan_id
LEFT JOIN `subscription_plans` p ON (p.id = s.plan_id)
WHERE s.user_id=1 AND  sv.videoid = 5
AND  v.videoid = 5
AND  DATE_ADD(FROM_UNIXTIME(s.`subscribed_on`), INTERVAL p.`days_limit` DAY) > CURRENT_DATE()

In above query i have an additional join subscription_plans to check your expiry date condition, also note you are using post variables directly in query i.e $userid=$_POST['userid'];$videoid=$_POST['videoid']; which is not safe and when you are using codeigniter then you should use active record library to build your query which will take of all escaping at its own end

Fiddle Demo

Here is the active record version of above query

$query = $this->db
    ->select('s.id')
    ->distinct()
    ->from('usersubscription s')
    ->join('subscribed_videos v ','s.plan_id = v.plan_id','LEFT')
    ->join('subscription_groups g ','s.id = assosiated_plan_id','LEFT')
    ->join('subscribed_videos sv','sv.plan_id = g.plan_id','LEFT')
    ->join('`subscription_plans` p','p.id = s.plan_id','LEFT')
    ->where('s.user_id',$userid)
    ->where('sv.videoid',$videoid)
    ->where('v.videoid',$videoid)
    ->where('DATE_ADD(FROM_UNIXTIME(s.`subscribed_on`), INTERVAL p.`days_limit` DAY) > CURRENT_DATE()',null,FALSE)
    ->get();