Showing posts with label Mysql Unix Timestamp. Show all posts
Showing posts with label Mysql Unix Timestamp. Show all posts

Friday, 2 August 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.

Thursday, 1 August 2019

Dates functions with MySQL and unix timestamp

MySQL has powerful functions built in to manipulate data with dates. These functions only work within MySQL providing your using their own date/time stamp. If your like me, your much rather use the PHP function time() to get the Unix timestamp and store that in the database. It seems a shame to miss out on these features because we use Unix time. Never fear, the following examples shows how to get round this using FROM_UNIXTIME(). So what happens now if we want to SELECT or GROUP data by periods, such as by week, month or year. Take one look in the MySQL manual and there are few practical examples on how this can be done using only the Unix timestamp. Below I run through a practical example and how this can easily be achieved. If you want a mock up table to try it out, run the following SQL. This table is a vastly simplified version of a e-commerce system order table, but the logic here could be applied to any type of database driven web site.
CREATE TABLE `orders` (
`order_ID` int(11) NOT NULL auto_increment,
`timeordered` int(11) NOT NULL default '0',
`grandtotal` double(10,2) NOT NULL default '0.00'
PRIMARY KEY  (`order_ID`)) ;
This table contains the Unix timestamp in `timeordered` and `grandtotal` contains a decimal number. We are going to construct a SQL query that will list the number of orders in a month, the total amount of all those orders and the average value of those orders. Lines 1, 2, 4 are standard functions used to calculate our values. The interesting lines are 5 and 6. If I was using the normal MySQL timstamp and could simply write GROUP BY MONTH(), YEAR() to achieve the results we want. You may ask why are we using both MONTH() and YEAR() when we just need it to group by MONTH(). Well the answer is you could just use MONTH(), but that would only ever return 12 rows (1 for each month) no matter over how many years. So for example it would group January 2004, January 2005 or any other January. For some systems this would be undesirable. Using YEAR() ensure only one month and one year is in one return row.
SELECT COUNT(order_ID) as `number`,
SUM(grandtotal) as `cgrandtotal`,
FROM_UNIXTIME(timeordered, '%M %Y') as `fdate`,
AVG(grandtotal) AS `averagegrand`
FROM `orders`
GROUP BY MONTH(FROM_UNIXTIME(timeordered,'%Y-%m-%d %H.%i.%s')),
YEAR(FROM_UNIXTIME(timeordered,'%Y-%m-%d %H.%i.%s'))
ORDER BY timeordered DESC
Normally we put the column name in the brackets of the function of the MySQL date (ie YEAR(column-name)). But because we are using Unix time we must use the functionFROM_UNIXTIME(column-name-of-unixtime, format). The format parameter simply specifies how to turn the Unix time to a understandable timestamp for MySQL, the ones shown on lines 7 and 8 seem to work fine, I believe there may be shorter ones that can be used.
So the query above will return something like this (after adding your own dummy data!):
NUMBERCGRANDTOTALFDATEAVERAGEGRAND
8532.00January 200866.50
211201.20December 200757.20
171041.25November 200761.25
The example below assumes you have some basic knowledge of SQL and has been tested on MySQL 4.1.

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.

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)