Found 4219 Articles for MySQLi

We can use COUNT(*) and GROUP BY clause to find out the repetition of a value in the column. Following is the example, using COUNT(*) and GROUP BY clause on ‘Name’ column of table ‘Student’, to demonstrate it −mysql> select count(*), name from student group by name; +----------+---------+ | count(*) | name    | +----------+---------+ | 1        | Aarav   | | 2        | Gaurav  | | 1        | Harshit | +----------+---------+ 3 rows in set (0.00 sec)The result set of above query shows that which value is repeated ... Read More

It is because without GROUP BY clause the output returned by MySQL can mislead. We are giving following example on the ‘Student’ table given below, to demonstrate it −mysql> Select * from Student; +------+---------+---------+-----------+ | Id   | Name    | Address | Subject   | +------+---------+---------+-----------+ | 1    | Gaurav  | Delhi   | Computers | | 2    | Aarav   | Mumbai  | History   | | 15   | Harshit | Delhi   | Commerce  | | 20   | Gaurav  | Jaipur  | Computers | +------+---------+---------+-----------+ 4 rows in set (0.00 sec) mysql> ... Read More

When we pass an out-of-range value in UNIX_TIMESTAMP, MySQL returns 0. The valid range of value is same as for the TIMESTAMP data type.Examplemysql> Select UNIX_TIMESTAMP('1969-01-01 04:05:45'); +---------------------------------------+ | UNIX_TIMESTAMP('1969-01-01 04:05:45') | +---------------------------------------+ |                         0             | +---------------------------------------+ 1 row in set (0.00 sec)When we pass an out-of-range value in FROM_UNIXTIME, MySQL returns NULL. The valid range of values is same as for the INTEGER data type.Examplemysql> Select FROM_UNIXTIME(2147483648); +---------------------------+ | FROM_UNIXTIME(2147483648) | +---------------------------+ | NULL                      | +---------------------------+ 1 row in set (0.00 sec)

We can use FROM_UNIXTIME() function to retrieve the value, as MySQL TIMESTAMP, stored as INT in the column of a table.For example, we have a table called ‘test123’ which has a column named ‘val1’. In this column, we stored the integer values as follows −mysql> Select * from test123; +------------+ | val1       | +------------+ |     150862 | | 1508622563 | |  622556879 | | 2147483647 | +------------+ 4 rows in set (0.00 sec)Now with the help of the FROM_UNIXTIME() function, we can retrieve the column integer values in the form of MySQL TIMESTAMP data.mysql> Select ... Read More

As we know that the value of timestamp can be converted to a number of seconds with the help of UNIX_TIMESTAMP() function. MySQL would ignore the microseconds added to the value of timestamp because the value of UNIX_TIMESTAMP is only 10digits long.Examplemysql> SELECT UNIX_TIMESTAMP('2017-10-22 04:05:36')AS 'Total Number of Seconds'; +-------------------------+ | Total Number of Seconds | +-------------------------+ | 1508625336              | +-------------------------+ 1 row in set (0.00 sec) mysql> SELECT UNIX_TIMESTAMP('2017-10-22 04:05:36.200000')AS 'Total Number of Seconds'; +-------------------------+ | Total Number of Seconds | +-------------------------+ | 1508625336              | +-------------------------+ ... Read More

In MySQL, UNIX timestamp format is the way to represent time value as an integer. The integer value represented for the date value would be the number of seconds. The starting date for counting these number of seconds is ‘1970-01-01’.mysql> SELECT UNIX_TIMESTAMP('2017-10-22 04:05:36')AS 'Total Number of Seconds'; +-------------------------+ | Total Number of Seconds | +-------------------------+ |              1508625336 | +-------------------------+ 1 row in set (0.00 sec)The UNIX_TIMESTAMP value is 10 digits long.

We have to use GROUP BY clause if we want to use group functions with non-group fields in the SELECT query. The general syntax can be as followsSyntaxSELECT group_function1, …, non-group-column1, … from table_name GROUP BY column_name;Examplemysql> Select COUNT(*), id from Student GROUP BY id; +----------+------+ | COUNT(*) | id   | +----------+------+ | 1        | 1    | | 1        | 2    | | 1        | 15   | | 1        | 17   | | 1        | 20   | +----------+------+ 5 ... Read More

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.Examplemysql> 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 ... Read More

As we know that group function operates on the sets of value that is why if group functions will be used in SELECT clause then they will be used on the rows that meet the query selection criteria and the output of group functions will be returned as the output of the query.ExampleIn the example below, we have used some group functions in a SELECT statement on the fields of ‘Student’ table and the output of the statement is the output of those group functions −mysql> Select COUNT(Name), MIN(Id), AVG(Id), MAX(Id), COUNT(*) from Student; +-------------+---------+---------+---------+----------+ | COUNT(Name) | MIN(Id) | ... Read More

As we know that the range of TIME field in MySQL is ‘-838:59:59’ to ‘838:59:59’. Now, if TIMEDIFF() function’s output surpasses this range then MySQL will return either ‘-838:59:59’ or ‘838:59:59’ depends upon the values of the argument. Example mysql> Select TIMEDIFF('2017-09-01 03:05:45', '2017-10-22 03:05:45')AS 'Out of Range TIME Difference'; +------------------------------+ | Out of Range TIME Difference | +------------------------------+ | -838:59:59 | +------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> Select TIMEDIFF('2017-10-22 04:05:45', '2017-09-01 03:05:45')AS 'Out of Range ... Read More