SQL Tutorial part-10 MySQL DATE TIME Difference

SQL Tutorial part-10 MySQL DATE TIME Difference
Image

Query from the DATETIME field in the database to find out the difference / distance between two datimes is very easy and there are many methods. For example, the table in this tutorial will show you how to find different types of queries with different types of queries.

If you want to see how long ago the registration date of all users in the “users” table from the current time

This can be done using the DATEDIFF () function in queries. Both of the two parameters of this function must be dated. This will find the distance between the two dates (days only). Remember that calculations are done only in days (part of date). Do the first parameter with big date and the second with small date then you will get positive result otherwise the result will show minus. See below I have given the first parameter NOW () to find the distance of the current time from the date of the “reg_date” column because the current time with NOW () is considered by MySQL.

Image

Output:

Image

If there are two datitime fields and you want to find the distance between them, then you have to enter the names of the two columns inside DATEDIFF ().

Image

If you want to find out the difference between the two periods

You can use the TIMEDIFF () function just to find out the time difference. There are also two parameters here and giving both DATETIME will find out the difference between them only by taking part of time. E.g.

Image

Output

00:25:

When you need to find the time difference between two DATETIME fields in a table, name the two columns as parameters. E.g.

Image

If you want to see all the differences between the two DATETIME, then there is another function name TIMESTAMPDIFF (). Here you have to enter the 1st parameter. The 2nd parameter will be the larger DATETIME and the 3rd parameter will be the other DATETIME. Just want to see the interval of days in the example below.

Image

Output

If you want to see the difference in seconds, try “SECOND” instead of “DAY” so you can verify others.

If you want to see all the hours, minutes and seconds at once, you need to enter the number of seconds as a parameter in the SEC_TO_TIME () function. E.g.

Image

Output

-24: 25:

This means that the interval between the two times is 24 hours 25 minutes and 15 seconds. Name the columns in the table in place of the two DATETIME, when you need to find the distance between the two DATETIME fields. Here TIMESTAMPDIFF (SECOND,: 06: 03 00:25:20′,: 06: 02 00:00:00′) returns the interval between the two dates as seconds.

Thus there are many more functions to find out the date time. Use according to your needs. Here are just a few examples, you can easily use other functions if you understand them. You can find the full list of functions on MySQL’s site.

Database Backup & Restore :

Here’s how to back up MySQL databases. For this I will use phpMyAdmin. First type http: // localhost / phpmyadmin / in the address bar of the browser then a list of all the databases created on the left will appear, then click on the database you want to backup. Click on the Export tab in the window that appears. As you can see in the picture below, I selected the “developer” database and clicked on the “Export” tab.

Image

Now a window will appear with many options to save the database. By default all tables are selected and SQL Format (SQL) is selected. Now you click on the Go button below and save. The diameter “developer” database will be saved in .sql format. If you want to export to another format, select it from the “Format” dropdown.

Image

Sometimes when the database is too big, you can export the database to zip or other compression systems. In that case, there is a radio button “Custom – display all possible options” in the image above, select it, then the option to download the database in zip format will come. As you can see in the picture below, a dropdown of “Compression” has occurred. From here, select any one of the compression methods and click “Go” to start the download.

Image

Suppose you need SQL code to create the database, then look at the picture “View output as text” There is a radio button, check here “Go” and the database will show the SQL code as output. If you copy and run this sequel code, the database will be created. There are many more options like this. We discussed 2 (1. Compressing and exporting, 2. Finding the SQL code to create the database) because these two are more useful most of the time.

How to restore the database

 

Suppose you restore the database that you backed up. Restore is usually done when the site is hacked or there is a problem with the database. To test here, you can create a new database. I created a database called ‘gg’. Now open this database as before and look at the Import tab next to the tab on which you exported.

Browse the location of the database you previously backed up by clicking on this tab, then click the Go button below to restore the database. I have done the same as you can see in the picture below.

Image

Here you can select the database of any format (you can also import the format that can be exported, such as .sql, .sql.zip, etc.)

 

 

 

If you are a cPanel, after login you will see the “Databases” section below. Clicking on the phpmyadmin icon will bring up the phpMyAdmin window just like the localhost. The next task is the same as the localhost.

You can restore the database in a similar way-

 

1. Click on gg database.

 

2. Click the SQL tab and paste the database code. (Open the database you have exported to Notepad and get the database code)

 

3. Now click on the Go button to restore the database.

Cipanel has another system for backing up databases-

 

If you login to Sypanel and click on the Backup icon from the Files section, you will see that there is an option to take a full or partial (such as database or home directory only) backup. Clicking on Home Directory from here will start downloading the full backup of the site. If you click on the database of your site in the DATABASES section below, only the database will be downloaded. You can restore them later if you need to. (See the image on the right. There is a Browse button to restore them.)

Image

Leave a Reply

Your email address will not be published. Required fields are marked *