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
MySQLi Articles
Page 118 of 341
How can we know about the starting range of TIMESTAMP data type with the help of MySQL FROM_UNIXTIME() function?
As we know that this function converts a number of seconds into TIMESTAMP value. So by providing 0 seconds as the argument, it will give us the starting range of TIMESTAMP data type.mysql> Select FROM_UNIXTIME(0); +-------------------------+ | FROM_UNIXTIME(0) | +-------------------------+ | 1970-01-01 05:30:00 | +-------------------------+ 1 row in set (0.00 sec)Now if we will change the argument from 0 to 60 seconds then the time will be changed by 01 minutes.mysql> Select FROM_UNIXTIME(60); +-------------------------+ | FROM_UNIXTIME(60) | +-------------------------+ | 1970-01-01 05:31:00 | +-------------------------+ 1 row in set (0.00 sec)
Read MoreHow does comparison operator work with date values in MySQL?
Comparison operator between dates will work in a logical way. In the following example, while comparing two dates, MySQL is simply comparing two numbers or string −mysql> select 20171027 < 20150825; +---------------------------+ | 20171027 < 20150825 | +---------------------------+ | 0 | +---------------------------+ 1 row in set (0.00 sec)The 0 output shows that the result of the above query is FALSE.mysql> select 20171027 > 20150825; +--------------------------+ | 20171027 > 20150825 | +--------------------------+ | 1 | +--------------------------+ 1 row in set (0.00 sec)The output ‘1’ shows that the result of the above query is TRUE.
Read MoreHow should I display MySQL database that is currently in use?
We can display the name of MySQL database that is currently in use by Select Database() command.mysql> select database(); +------------+ | database() | +------------+ | tutorial | +------------+ 1 row in set (0.00 sec)This command shows that we currently use tutorial database.
Read MoreHow to check statement of creating a particular MySQL database?
With the help of CREATE DATABASE db-name command, we can check the statement of creating any MySQL database.mysql> SHOW CREATE DATABASE Sample; +----------+-------------------------------------------------------------------+ | Database | Create Database | +----------+-------------------------------------------------------------------+ | sample | CREATE DATABASE `sample` /*!40100 DEFAULT CHARACTER SET latin1 */ | +----------+-------------------------------------------------------------------+ 1 row in set (0.00 sec)The output shows how MySQL database named Sample has been created.
Read MoreHow can I calculate full 24hour days between two specified dates in MySQL?
In DATEDIFF() function only the date parts of the values are used in calculation hence we can use TIMESTAMPDIFF() function to calculate full 24 hour days between two specified dates.For example, if we want to find full 24hour days between ‘2017-05-27 11:59:00’ and 2017-05-23 12:00:00’ then following would be MySQL query −mysql> Select TIMESTAMPDIFF(DAY, '2017-05-23 12:00:00' , '2017-05-27 11:59:00'); +---------------------------------------------------------------------------+ | TIMESTAMPDIFF(DAY, '2017-05-23 12:00:00' , '2017-05-27 11:59:00') | +---------------------------------------------------------------------------+ | 3 | +---------------------------------------------------------------------------+ 1 row in set (0.00 sec)
Read MoreIn MYSQL, how can we store a date where the day, month or both month & day are zero?nday are zero?
To store such kind of dates where the day, month or both month & day are zero we must have to set mode of sql to allow_invalid_dates mode.mysql> set sql_mode = 'allow_invalid_dates'; Query OK, 0 rows affected (0.00 sec) mysql> insert into check_date(OrderDate) values('2017-00-00'); Query OK, 1 row affected (0.06 sec) mysql> select * from check_date; +-------------+ | OrderDate | +-------------+ | 2017-00-00 | +-------------+ 1 row in set (0.00 sec)Above query will insert the date in which both month & day values are zero.mysql> insert into check_date(Orderdate) values ('2017-00-05'); Query OK, 1 row affected (0.07 sec) ...
Read MoreHow can I store '0000-00-00' as a date in MySQL?
For storing the date like ‘0000-00-00’ in a column of MySQL table, we must have to set the SQL mode to ‘allow_invalid_date’. Following example will demonstrate it −mysql> SET sql_mode = 'allow_invalid_dates'; Query OK, 0 rows affected, 1 warning (0.03 sec) mysql> Create table test_date(date_order date); Query OK, 0 rows affected (0.45 sec) mysql> Insert into test_date(date_order) values('0000-00-00'); Query OK, 1 row affected (0.04 sec) mysql> Select * from test_date; +------------+ | date_order | +------------+ | 0000-00-00 | +------------+ 1 row in set (0.00 sec)
Read MoreHow can we create our own choice MySQL database?
CREATE DATABASE db_name can be used to create our own choice MySQL database. For example to create a database named Sample, we should have to run the command as follows −mysql> CREATE DATABASE Sample; Query OK, 1 row affected (0.04 sec)
Read MoreWhat happens when MySQL encounters an out-of-range date?
The response of MySQL on encountering out-of-range or invalid date will depend upon SQL MODE. If we have enabled ALLOW_INVALID_DATES mode then MySQL will convert the out of range values into all zeros (i.e. ‘0000:00:00 00:00:00’) and also stores the same in the table without producing any error or warning.For example, we can change SQL MODE as follows and then insert the out-of-range −mysql> set sql_mode = 'ALLOW_INVALID_DATES'; Query OK, 0 rows affected (0.00 sec) mysql> Insert into order1234(productname, quantity, orderdate) values('A', 500, '999-05-100'); Query OK, 1 row affected, 1 warning (0.13 sec) mysql> Select * from order1234; ...
Read MoreHow can we allow MySQL to store invalid dates?
After enabling the SQL MODE to ALLOW_INVALID_DATES, MySQL will also be able to store invalid dates in the table. The example is given below to understand it −mysql> Insert into order1234(ProductName, Quantity, Orderdate) values('B',500,'2015-11-31'); Query OK, 1 row affected (0.06 sec) mysql> Select * from order1234; +-------------+----------+--------------+ | ProductName | Quantity | OrderDate | +-------------+----------+--------------+ | A | 500 | 0000-00-00 | | B | 500 | 2015-11-31 | +-------------+----------+--------------+ 2 rows in set (0.00 sec)We can see MySQL also inserts the invalid date in a table.
Read More