Showing posts with label Mysql FROM_UNIXTIME. Show all posts

Tuesday, 30 July 2019

How to convert from Unix timestamp to MySQL timestamp value?

MySQL converts Unix timestamp to timestamp data type value with the help of FROM_UNIXTIME() function.

Example:

mysql> Select FROM_UNIXTIME(1508622563);
+-----------------------------+
| FROM_UNIXTIME(1508622563)   |
+-----------------------------+
| 2017-10-22 03:19:23         |
+-----------------------------+
1 row in set (0.00 sec)

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

Saturday, 8 September 2018

Convert a UNIX timestamp to a datetime with MySQL

Dates and times can be stored as an integer value as a UNIX timestamp. This isn't a particularly human readable format but can be converted in MySQL to a datetime value using the FROM_UNIXTIME function.

Dates and times as timestamps

Back when I first started using MySQL and PHP and created an ecommerce system that was used on a number of websites, I decided to store datetimes as a UNIX timestamp for whatever reason. This made it easy to use the PHP date function to format the dates but is not so useful when browsing data in the database or selecting date ranges in a query. I no longer do this and always store date times as a datetime field type now.

Converting integer timestamp to a datetime

In my old ecommerce system there was a table called "orders" with a column called "date_ordered" which was a UNIX timestamp stored as an integer. To convert this to a datetime as part of the SELECT query this needs to be done:

SELECT other fields here, FROM_UNIXTIME(date_ordered)
FROM orders
WHERE ...

The integer field will now be represented in the resultset as a datetime string in the format YYYY-MM-DD HH:MM:SS.

Monday, 3 September 2018

Mysql select the date using the timestamp

In my database i use a column named `startdate` and in the column there are rows with timestamps, looking like: `1410178260`

Normally, when i use a datetime field and i want to select all the items with the date of today, i run this query:

$sql = "SELECT id FROM agenda2 WHERE DATE(startdate) >= CURRENT_DATE()";

But now, using the timestamps, i don't know how to make a query that selects all the items inserted today. Can someone help me with that?

You need to convert to date using the function from_unixtime()

mysql> select FROM_UNIXTIME('1410178260');
+-----------------------------+
| FROM_UNIXTIME('1410178260') |
+-----------------------------+
| 2014-09-08 17:41:00         |
+-----------------------------+

So you may do as

SELECT id FROM agenda2 WHERE DATE(FROM_UNIXTIME(startdate)) >= CURRENT_DATE()

Tuesday, 28 August 2018

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();

Tuesday, 30 July 2019

Example:

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

Quick summary

The Mini-Course

Converting to readable dates

Grouping Epochs

Adding a new record to our database

Negative Epochs

MySQL date format specifiers

Saturday, 8 September 2018

Dates and times as timestamps

Converting integer timestamp to a datetime

Related posts:

Monday, 3 September 2018

Tuesday, 28 August 2018

Total Pageviews

Achievement

Live Traffic

Subscribe To

Followers

About Me

I V RAMANA

Recent Comments

Categories

Popular Posts

Hot Topics

Video

News

Comments

Recent

Bottom Ad [Post Page]

Recent Posts

Socialize

Blog Archive

Search This Blog

Post Top Ad

Archive

Post Bottom Ad

Author Details

About Me

Tags

Full width home advertisement

Pages

Post Page Advertisement [Top]

Climb the mountains