SQL Tutorial part :9 How to view time zone and open connection of MY SQL Server

SQL Tutorial part :9 How to view time zone and open connection of MY SQL Server
Image

Timezone of MySQL Server

We know how timezones are viewed and set in PHP. I also know that the timezone can be changed if desired. E.g.

Image

It’s easy. But many times it is necessary to look at the timezone of MySQL server. It is often seen that the date of your application is not showing correctly. It was fine when Lokali was working but the time on the live is looking different. These problems are usually more common if the timestamp field is somewhere in the table of your database. Because when data is saved, it is saved according to that timezone. However, with a small query you can see the timezone of your MySQL server

Image

Run the top query from the Query tab of your HeidiSQual or PHP MyAdmin will show the timezone as below.

Image

I have run it locally so you can see it run on your production server.

View MySQL open connections etc.

Run the following query to see how many connections are open in your database at this time.

Image

Output (Threads_connected is the number of open connections)

Image

Many times the application shows “Too many connections” error, this error shows if more connections are open. Then this query can see how many connections are open. By default 151 maximum open connections can run. You can increase it if you need more. If the production server is shared, you will not be able to customize it.

Not even with PHPMyAdmin from cPanel. If root access means a dedicated server, then you can customize this default value by logging in with WHM and then going to PHPMyAdmin. Searching from the “Variables” tab of PHPMyAdmin (for example, typing max conn will bring up the value of the maximum connection variable) will move the mouse over the link and the “Edit” link will appear. Locally you can see these tests.

Image

Differences and usage between My SQL DateTime and Timestamp

When working with My SQL Data Time, you must have heard the names datetime and timestamp data types, both of which can be dated. During the design of the database, it is often doubtful whether the datatype or TIMESTAMP of any one field will be given. This tutorial will discuss the differences between them and when to use them.

Image

Difference 1: There are many differences between DATETIME and TIMESTAMP but the main difference is with timezone. When you enter the datetime in the DATETIME field, the date you entered will show up everywhere, never changing, even if the timezone changes. And if you enter the date time in the TIMESTAMP field, it will change and save the UTC (Universal Time) timezone from the timezone of your server. Then when you pick up that date time (in your application) then change it from UTC to the current timezone (the timezone of the server you are picking up) and then show it.

Suppose you created a database with a TIMESTAMP field and entered a lot of datatimes here. And this work has been done by Asian in any timezone. Now you have sent this database to your friend who lives in USA. When he imports the database to an American timezone, the datimes in the TIMESTAMP field will be automatically updated according to that American timezone. Even if you set the date in the Asian timezone, your friend will see those dates according to the American timezone. These changes would not have happened if the DATETIME field had not been TIMESTAMP.

Difference 2: Another difference is the deadline. The range of DATETIME is from ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59′ while the range of TIMESTAMP is’ 1970-01-01 00:00:01 ‘from UTC to’ 2038 -01-09 03:14:07 ‘

Difference 3: Indexing can be done at TIMESTAMP and works fast again on the other hand DATETIME is a little slow. However, these have been recovered in the latest version of MySQL. Testing the unit shows which one is working faster.

When to use which?

It is better to use TIMESTAMP when you have to keep a record of what is changing in the database and use DATETIME when that field changes from outside (application). Suppose a row is inserted in your database or TIMESTAMP to keep track of the fields when the user updated an item. TIMESTAMP is also used in real time applications such as comet type technology to keep track of database changes. DATETIME in most other cases.

Leave a Reply

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