Article Categories
- All Categories
-
Data Structure
-
Networking
-
RDBMS
-
Operating System
-
Java
-
MS Excel
-
iOS
-
HTML
-
CSS
-
Android
-
Python
-
C Programming
-
C++
-
C#
-
MongoDB
-
MySQL
-
Javascript
-
PHP
-
Economics & Finance
MySQL Articles
Page 125 of 355
How can we create a MySQL function to find out the duration of years, months, days, hours, minutes and seconds?
Following is a MySQL function which calculates the duration in years, months, days, hours, minutes and seconds between two dates.mysql> DROP FUNCTION IF EXISTS Duration; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> DROP FUNCTION IF EXISTS Label123; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> DELIMITER // mysql> CREATE FUNCTION Duration( dtd1 datetime, dtd2 datetime ) RETURNS CHAR(128) -> BEGIN -> DECLARE yyr, mon, mmth, dy, ddy, hhr, m1, ssc, t1 BIGINT; -> DECLARE dtmp DATETIME; -> DECLARE t0 TIMESTAMP; -> SET yyr = TIMESTAMPDIFF(YEAR, dtd1, dtd2); ...
Read MoreWhat MySQL returns on passing an invalid string as an argument to STR_TO_DATE() function?
If we pass an invalid string as an argument to STR_TO_DATE() function then MySQL will return NULL as output along with a warning. Following is an example to understand the same −mysql> Select STR_TO_DATE('20173210', '%Y%d%m'); +-----------------------------------+ | STR_TO_DATE('20173210', '%Y%d%m') | +-----------------------------------+ | NULL | +-----------------------------------+ 1 row in set, 1 warning (0.00 sec)In the query above the string value is invalid because of wrong (32) day value. Hence it returns NULL values and a warning which is given below.mysql> Show warnings\G *************************** 1. row *************************** ...
Read MoreHow can we update values in a MySQL table?
With the help of UPDATE statement and WHERE clause, we can update the values in single or multiple rows of the table. MySQL updates the values on the basis of condition specified in WHERE clause. For example, suppose in the ‘employee’ table we want to change the ‘name’ and ‘doj’ of the employee whose id is 1 then it can be done with the following query −mysql> UPDATE employee SET name = 'Gaurav', doj = '2010-02-01' WHERE id = 1; Query OK, 1 row affected (0.06 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from employee ...
Read MoreHow to use MySQL FROM_UNIXTIME() function to return datetime value in numeric format?
As we know that we can convert a time of datetime value to an integer by adding 0(+0) to them. In a similar way, we can convert the datetime value returned by FROM_UNIXTIME() function in numeric format. The following example will clarify it more −mysql> Select FROM_UNIXTIME(1555033470)+0 AS 'Date in Numeric Format'; +------------------------+ | Date in Numeric Format | +------------------------+ | 20190412071430.000000 | +------------------------+ 1 row in set (0.00 sec)After adding 0 (+0) to datetime value MySQL returns the numeric value up to 6 digits microseconds.
Read MoreHow can we specify default values in MySQL INSERT statement?
At the time of creation of a table, if any column is defined with default values then by using the keyword ‘DEFAULT’ in the INSERT statement, we can take default value for that column. For example, we have created a table ‘employee’ with a default value of column ‘DOJ’ as follows −mysql> Create table employee(id int, name varchar(20), doj date DEFAULT '2005-01-01'); Query OK, 0 rows affected (0.09 sec) mysql> Insert into employee(id, name, doj) values(1, ’Aarav’, DEFAULT); Query OK, 1 row affected (0.03 sec) mysql> select * from employee; +------+------------+---------------+ | id | name ...
Read MoreWhat is the proper way to retrieve the value stored in INT column as MySQL TIMESTAMP?
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 MoreWhy should we not use group functions with non-group fields without GROUP BY clause in MySQL SELECT query?
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 MoreHow can we know the repetition of a value in column with the help of group function COUNT(*) and GROUP BY clause?
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 ...
Read MoreHow can I use 2-digit year value in MySQL DATEDIFF() function?
We can use 2-digit year value either in single date expression or in both date expressions used as argument/s in MySQL DATEDIFF() function.For example, the query below is using 2-digit year value in first date expression and other is having 4-digit year value.mysql> Select DATEDIFF('18-10-22', '2017-10-22'); +-----------------------------------+ | DATEDIFF('18-10-22', '2017-10-22') | +-----------------------------------+ | 365 | +-----------------------------------+ 1 row in set (0.00 sec)And the query below is using 2-digit year value in both date expressions.mysql> Select DATEDIFF('18-10-22', '17-10-22'); +---------------------------------+ | DATEDIFF('18-10-22', '17-10-22') | +---------------------------------+ | ...
Read MoreWhat is the range of date time value that we can pass as an argument to MySQL UNIX_TIMESTAMP function?
The range of date time value that we can pass as an argument to MySQL UNIX_TIMESTAMP function is the same as the range of TIMESTAMP data type i.e. between ‘1970-01-01 00:00:01’ to ‘2038-01-19 08:44:07’. If we give the date time values in UNIX_TIMESTAMP function beyond or below TIMESTAMP range, MySQL will return 0 as output. It can be understood with the help of the following example −mysql> select UNIX_TIMESTAMP('2038-01-19 08:44:07'); +---------------------------------------+ | UNIX_TIMESTAMP('2038-01-19 08:44:07') | +---------------------------------------+ | 2147483647 | +---------------------------------------+ 1 row in set (0.00 sec) ...
Read More