Showing posts with label Mysql UNIX_TIMESTAMP. Show all posts
Showing posts with label Mysql UNIX_TIMESTAMP. Show all posts

Thursday, 1 August 2019

MySQL：UNIX_TIMESTAMP

mysql> INSERT INTO abc (id, timestamp) VALUES ('', UNIX_TIMESTAMP());

mysql> SELECT NOW() AS DATETIME;
+---------------------+
| DATETIME            |
+---------------------+
| 2014-01-20 17:25:27 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT UNIX_TIMESTAMP() AS TIMESTAMP;
+------------+
| TIMESTAMP  |
+------------+
| 1390206355 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT UNIX_TIMESTAMP('2013-06-15 19:37:33') AS TIMESTAMP;
+------------+
| TIMESTAMP  |
+------------+
| 1371292653 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT FROM_UNIXTIME(1371292653) AS DATETIME;
+---------------------+
| DATETIME            |
+---------------------+
| 2013-06-15 19:37:33 |
+---------------------+
1 row in set (0.00 sec)

Tuesday, 30 July 2019

How to convert a MySQL Datetime field to a Unix Timestamp using PHP

In the example you’ll find a handy PHP function that will do the conversion.
<?php 
 
$DATETIME = '2010-04-22 19:54:38';
 
echo convertDateTime($DATETIME);
 
/*
 * This function converts a mysql datetime to a unix timestamp
 * The format should be "YYYY-MM-DD HH:MM:SS"
 *
 * @param $datetime string Contains the DATETIME value
 * @return UNIX TIMESTAMP
 */
function convertDateTime($datetime) {
    list($date, $time) = explode(' ', $datetime);
    list($year, $month, $day) = explode('-', $date);
    list($hours, $minutes, $seconds) = explode(':', $time);
    
    $UnixTimestamp = mktime($hours, $minutes, $seconds, $month, $day, $year);
    return $UnixTimestamp;
}
 
?>
First we divide the date from the time and then break up the date into year, month and day variables. After that we break the time up into hour, minute and second and then we create a Unix Timestamp using the mktime PHP function.

Convert Unix timestamp into human readable date using MySQL

Is there a MySQL function which can be used to convert a Unix timestamp into a human readable date? I have one field where I save Unix times and now I want to add another field for human readable dates.

select from_unixtime(column_name, '%Y-%m-%d') from table_name

 
SELECT theTimeStamp, FROM_UNIXTIME(theTimeStamp) AS readableDate
               FROM theTable
               WHERE theTable.theField = theValue;

 
mysql> select convert_tz(from_unixtime(1467095851), 'UTC', 'MST') as 'local time';

+---------------------+
| local time          |
+---------------------+
| 2016-06-27 23:37:31 |
+---------------------+

 
SELECT
  from_unixtime(timestamp, '%Y %D %M %H:%i:%s')
FROM 
  your_table

 
SELECT
  FROM_UNIXTIME(timestamp) 
FROM 
  your_table;

MySQL – Unix Timestamp

1. How to make field store Unix timestamp value ?

If you know how to enable and store Timestamp then you can skip to next section.
For this, you need to set or create a column in your Table which able to take Integer value (e.g. DataTypes – INT,MEDIUMINT, BIGINT) and set its size to 11 or more.
If you are using PHP within your project then you can use the time() function to get current Unix Timestamp.
$timestamp = time();
echo $timestamp;    // 1477015564

2. Select and Convert

For converting a timestamp you can use FROM_UNIXTIME.
Syntax – 
FROM_UNIXTIME( timestamp[, format] )
Parameters
  • The first parameter is your Timestamp value or field name.
  • This is an optional parameter which you can use to set your specific format.
If you are using only one parameter then it will return value in 'YYYY-MM-DD HH:MM:SS' format.

Example

For demonstration, I am using users table.
Table Structure
CREATE TABLE `users` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `username` varchar(80) NOT NULL,
  'fullname' varchar(60) NOT NULL,
  'timestamp' int(11) NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Inserted 3 entries on it.
idusernamefullnametimestamp
1yssyogeshYogesh singh1476928222
2sonarikaSonarika Bhadoria1476929693
3vishalVishal Sahu1476930908
SELECT Query
SELECT 
username,
FROM_UNIXTIME(timestamp) as timestamp 
FROM `users`
When you run this query this gives the following output –
usernametimestamp
yssyogesh2016-10-20 07:20:22
sonarika2016-10-20 07:44:53
vishal2016-10-20 08:05:08

3. Specify Date format

For changing format, you need to specify the second parameter in the function.
SELECT Query
SELECT 
username,
FROM_UNIXTIME(timestamp,'%a - %D %M %y %H:%i:%s') as timestamp 
FROM `user`
Output
usernametimestamp
yssyogeshThu – 20th October 16 07:20:22
sonarikaThu – 20th October 16 07:44:53
vishalThu – 20th October 16 08:05:08

4. Conclusion

Now you know how to convert your timestamp field to specific Date Time format within your SELECT query and you can use it directly in your program.

MySQL – Convert Unix Timestamp to Date Time

I think you already know that the Unix Timestamp is not Human readable values it is just set of Integer numbers to the user when you show it on the screen.
Because of this reason we need to convert it before present it to the user.
If you do not convert the value while selecting rows from the Database Table then you are doing this programmatically after SELECT.
In the PHP you can use the date() function for converting.
$timestamp = 1476956996;
echo "date time : ".date('d-M-Y H:i:s a',$timestamp);
This gives the following output –
date time : 20-Oct-2016 11:49:56 am
In simple terms, the Unix timestamp stores the current Date Time in Integer format.
In this short tutorial, I show how you can convert Unix Timestamp value to readable Date Time format when you select data from the MySQL Database Table.

Mysql - What is the difference between UNIX TIMESTAMPS and MySQL TIMESTAMPS?

In MySQL, UNIX TIMESTAMPS are stored as 32-bit integers. On the other hand MySQL TIMESTAMPS are also stored in similar manner but represented in readable YYYY-MM-DD HH:MM:SS format.
Example:
mysql> Select UNIX_TIMESTAMP('2017-09-25 02:05:45') AS 'UNIXTIMESTAMP VALUE';
+---------------------+
| UNIXTIMESTAMP VALUE |
+---------------------+
| 1506285345          |
+---------------------+
1 row in set (0.00 sec)
The query above shows that UNIX TIMESTAMPS values are stored as 32 bit integers whose range is same as MySQL INTEGER data type range.
mysql> Select FROM_UNIXTIME(1506283345) AS 'MySQLTIMESTAMP VALUE';
+----------------------+
| MySQLTIMESTAMP VALUE |
+----------------------+
| 2017-09-25 01:32:25  |
+----------------------+
1 row in set (0.00 sec)
The query above shows that MySQL TIMESTAMPS values are also stored as 32 bit integers, but in a readable format, whose range is same as MySQL TIMESTAMP data type range.

Using Unix Timestamps in MySQL

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

Quick summary

GoalMySQL query
Get current epoch timeSELECT UNIX_TIMESTAMP(NOW()) (now() is optional)
Today midnightSELECT UNIX_TIMESTAMP(CURDATE())
Yesterday midnightSELECT UNIX_TIMESTAMP(DATE_ADD(CURDATE(),INTERVAL -1 DAY))
Jan 1 of current yearSELECT UNIX_TIMESTAMP(CONCAT(YEAR(CURDATE()),'-01-01'))
Convert from date to epochSELECT UNIX_TIMESTAMP(timestring)
Time format: YYYY-MM-DD HH:MM:SS or YYMMDD or YYYYMMDD
Convert from epoch to dateSELECT 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
%aAbbreviated weekday name (Sun..Sat)
%bAbbreviated month name (Jan..Dec)
%cMonth, numeric (0..12)
%DDay of the month with English suffix (0th, 1st, 2nd, 3rd, ...)
%dDay of the month, numeric (00..31)
%eDay of the month, numeric (0..31)
%fMicroseconds (000000..999999)
%HHour (00..23)
%hHour (01..12)
%IHour (01..12)
%iMinutes, numeric (00..59)
%jDay of year (001..366)
%kHour (0..23)
%lHour (1..12)
%MMonth name (January..December)
%mMonth, numeric (00..12)
%pAM or PM
%rTime, 12-hour (hh:mm:ss followed by AM or PM)
%SSeconds (00..59)
%sSeconds (00..59)
%TTime, 24-hour (hh:mm:ss)
%UWeek (00..53), where Sunday is the first day of the week
%uWeek (00..53), where Monday is the first day of the week
%VWeek (01..53), where Sunday is the first day of the week; used with %X
%vWeek (01..53), where Monday is the first day of the week; used with %x
%WWeekday name (Sunday..Saturday)
%wDay of the week (0=Sunday..6=Saturday)
%XYear for the week where Sunday is the first day of the week, numeric, four digits; used with %V
%xYear for the week, where Monday is the first day of the week, numeric, four digits; used with %v
%YYear, numeric, four digits
%yYear, numeric (two digits)
%%A literal '%' character

Subscribe to: Posts (Atom)