MySQL - TIMEDIFF() Examples

The MySQL TIMEDIFF() function returns the difference between two time or datetime values.

The way it works is, you provide the two values to compare, and TIMEDIFF() subtracts the second value from the first, then returns the result as a time value.

Syntax

The syntax goes like this:

TIMEDIFF(expr1,expr2)

Where expr1 and expr2 are the two values to compare. The return value is expr2subtracted from expr1.

Basic Example

Here’s an example to demonstrate.

SELECT TIMEDIFF('11:35:25', '10:35:25');

Result:

+----------------------------------+
| TIMEDIFF('11:35:25', '10:35:25') |
+----------------------------------+
| 01:00:00                         |
+----------------------------------+

Elapsed Time

The time value can represent elapsed time, so it’s not limited to being less than 24 hours.

SELECT TIMEDIFF('500:35:25', '10:35:25');

Result:

+-----------------------------------+
| TIMEDIFF('500:35:25', '10:35:25') |
+-----------------------------------+
| 490:00:00                         |
+-----------------------------------+

Negative Time Difference

If the second value is larger than the first, you’ll get a negative value for the time difference. This is perfectly valid.

SELECT TIMEDIFF('10:35:25', '500:35:25');

Result:

+-----------------------------------+
| TIMEDIFF('10:35:25', '500:35:25') |
+-----------------------------------+
| -490:00:00                        |
+-----------------------------------+

Datetime Values

Here’s an example that uses datetime values as the arguments.

SELECT TIMEDIFF('2021-02-01 10:35:25', '2021-01-01 10:35:25');

Result:

+--------------------------------------------------------+
| TIMEDIFF('2021-02-01 10:35:25', '2021-01-01 10:35:25') |
+--------------------------------------------------------+
| 744:00:00                                              |
+--------------------------------------------------------+

Note that both arguments must be of the same type. So you can’t have a time value for the first and a datetime value for the second (and vice-versa).

Also note that the time data type can only be in the range -838:59:59 to 838:59:59. Therefore, the following doesn’t work:

SELECT TIMEDIFF('2000-01-01 10:35:25', '2021-01-01 10:35:25');

Result:

+--------------------------------------------------------+
| TIMEDIFF('2000-01-01 10:35:25', '2021-01-01 10:35:25') |
+--------------------------------------------------------+
| -838:59:59                                             |
+--------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

In this case, we get an incorrect result and a warning.

MySQL - UNIX_TIMESTAMP() Examples

In MySQL, you can use the UNIX_TIMESTAMP() function to return a Unix timestamp. A Unix timestamp is the number of seconds that have elapsed since ‘1970-01-01 00:00:00’ UTC.

You can use this function to return a Unix timestamp based on the current date/time or another specified date/time.

Syntax

You can use any of the following forms:

UNIX_TIMESTAMP()
UNIX_TIMESTAMP(date)

The (optional) date argument allows you to specify a date for which to calculate the Unix timestamp. If provided, the function returns the value of the argument as seconds since ‘1970-01-01 00:00:00’ UTC.

The date argument can be a date, datetime, or timestamp string, or a number in YYMMDD, YYMMDDHHMMSS, YYYYMMDD, or YYYYMMDDHHMMSS format.

The return value is an integer if no argument is given or the argument does not include a fractional seconds part, or decimal if an argument is given that includes a fractional seconds part.

Example 1 – Using the Current Date/Time

This example uses the current date and time to produce the Unix timestamp.

SELECT UNIX_TIMESTAMP();

Result:

+------------------+
| UNIX_TIMESTAMP() |
+------------------+
|       1530054626 |
+------------------+

So that is how many seconds have passed since 00:00:00 Coordinated Universal Time (UTC), Thursday, 1 January 1970, and the time I ran that query.

Example 2 – Specify a Date

In this example, I provide a date for which to calculate the Unix timestamp from.

SELECT UNIX_TIMESTAMP('1970-01-02') As Result;

Result:

+--------+
| Result |
+--------+
|  50400 |
+--------+

Example 3 – Specify a Datetime Value

In this example, I provide a datetime value.

SELECT UNIX_TIMESTAMP('2021-11-27 12:35:03') AS Result;

Result:

+------------+
| Result     |
+------------+
| 1637980503 |
+------------+

Example 4 – Fractional Seconds

As mentioned, if you provide a fractional seconds part, the return value will be a decimalvalue (as opposed to integer for the previous examples).

Here’s an example.

SELECT UNIX_TIMESTAMP('2021-11-27 12:35:03.123456') AS Result;

Result:

+-------------------+
| Result            |
+-------------------+
| 1637980503.123456 |
+-------------------+

MySQL UPDATE with random number between 1-3

Got a big table and I want to add a column that has a randomly chosen number for each record. 1, 2, or 3.

Examples:

UPDATE tableName SET columnName = FLOOR( 1 + RAND( ) *3 );

UPDATE 'videos' SET 'views' = rand(1,10000);

Update videos set views = CAST(RAND() * 10000 AS UNSIGNED);

How to Check the Size of a Database in MySQL

In MySQL, you can query the information_schema.tables table to return information about the tables in a database. This table includes information about the data length, index length, as well as other details such as collation, creation time, etc. You can use the information in this table to find the size of a given database or all databases on the server.

You can also use the MySQL Workbench GUI to find details about the database (including its size).
This article provides a quick overview of both methods.

Code Example

Here’s an example of finding the size of each database by running a query against the information_schema.tables table:

SELECT 
    table_schema 'Database Name',
    SUM(data_length + index_length) 'Size in Bytes',
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) 'Size in MiB'
FROM information_schema.tables 
GROUP BY table_schema;

Result:

+--------------------+---------------+-------------+
| Database Name      | Size in Bytes | Size in MiB |
+--------------------+---------------+-------------+
| information_schema |             0 |        0.00 |
| Music              |         98304 |        0.09 |
| mysql              |       2506752 |        2.39 |
| performance_schema |             0 |        0.00 |
| sakila             |       6766592 |        6.45 |
| Solutions          |         16384 |        0.02 |
| sys                |         16384 |        0.02 |
| world              |        802816 |        0.77 |
+--------------------+---------------+-------------+

In this example I’ve listed the size in bytes and in mebibytes (MiB), but you can choose how you want to present it.
Of course, you can always narrow it down to a specific database if you need to. Simply add a WHERE clause with the name of the database:

SELECT 
    table_schema 'Database Name',
    SUM(data_length + index_length) 'Size in Bytes',
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) 'Size in MiB'
FROM information_schema.tables 
WHERE table_schema = 'sakila';

Result:

+---------------+---------------+-------------+
| Database Name | Size in Bytes | Size in MiB |
+---------------+---------------+-------------+
| sakila        |       6766592 |        6.45 |
+---------------+---------------+-------------+

The FORMAT_BYTES() Function

You can use the sys.FORMAT_BYTES() function to save yourself converting the size into mebibytes, kibibytes, or whatever. This function takes a value, converts it to human-readable format and returns a string consisting of a value and a units indicator. The converted value will depend on the size of the value (so the result could be in bytes, KiB (kibibytes), MiB (mebibytes), GiB (gibibytes), TiB (tebibytes), or PiB (pebibytes).
Here’s an example of rewriting the previous example to use the FORMAT_BYTES() function:

USE Music;
SELECT 
    table_schema 'Database Name',
    SUM(data_length + index_length) 'Size in Bytes',
    sys.FORMAT_BYTES(SUM(data_length + index_length)) 'Size (Formatted)'
FROM information_schema.tables 
GROUP BY table_schema;

Result:

+--------------------+---------------+------------------+
| Database Name      | Size in Bytes | Size (Formatted) |
+--------------------+---------------+------------------+
| information_schema |             0 | 0 bytes          |
| Music              |         98304 | 96.00 KiB        |
| mysql              |       2506752 | 2.39 MiB         |
| performance_schema |             0 | 0 bytes          |
| sakila             |       6766592 | 6.45 MiB         |
| Solutions          |         16384 | 16.00 KiB        |
| sys                |         16384 | 16.00 KiB        |
| world              |        802816 | 784.00 KiB       |
+--------------------+---------------+------------------+

MySQL Workbench

Another way of finding the database size is to use the MySQL Workbench GUI. Here’s how:

1. Navigate to the database in the Schemas pane
2. Hover over the applicable database
3. Click the little information icon beside the database name. This loads information about the database, including its approximate size, table count, collation, etc. The database size is listed on the Info tab (usually the default tab).

Save HTML Form Data in a (.txt) Text File in PHP

Hello Programmers, in this post I gonna show you a very essential task that can be easily done with Core PHP and HTML form.

Sometimes it happens that we need to store some data in local storage file rather than making it complex using the database. Yes, it’s a fact that in many cases we don’t want to store our text data in database always.
Here I am giving you an example, suppose you have an HTML form and you want to store the data submitted by the user in a text file so that you can easily access it later from that file without opening your database.

PHP Program to store HTML Form data in a .txt File

Below I have provided the PHP code to store the form data in a text file. Just took a glance at this code.
For easy understanding after the code, I have provided the explanation and how to use this code step by step.

1. <?php
3. if(isset($_POST['textdata']))
4. {
5. $data=$_POST['textdata'];
7. $fp = fopen('data.txt', 'a');
8. fwrite($fp, $data);
9. fclose($fp);
10. }
11. ?>

Here ‘textdata’ is the name of our HTML form field that is provided below.
data.txt is a file that we have to create for storing our form submission data in it.

$data is a PHP variable to store the form field data entered by the user.

Now the HTML part`

1. <!DOCTYPE html>
2. <html>
3. <head>
4. <title>Store form data in .txt file</title>
5. </head>
6. <body>
7. <form method="post">
8. Enter Your Text Here:<br>
9. <input type="text" name="textdata"><br>
10. <input type="submit" name="submit">
12. </form>
14. </body>
15. </html>

Now I think you have understood the thing.

remember to add method in your form. <form method=”post”>

Step by step guide on How to put the HTML form field data in a text file or dot txt file in PHP

1. Create a PHP file and put the below code and save it.
   1. <!DOCTYPE html>
   2. <html>
   3. <head>
   4. <title>Store form data in .txt file</title>
   5. </head>
   6. <body>
   7. <form method="post">
   8. Enter Your Text Here:<br>
   9. <input type="text" name="textdata"><br>
   10. <input type="submit" name="submit">
   12. </form>
   14. </body>
   15. </html>
   18. <?php
   20. if(isset($_POST['textdata']))
   21. {
   22. $data=$_POST['textdata'];
   24. $fp = fopen('data.txt', 'a');
   26. fwrite($fp, $data);
   27. fclose($fp);
   28. }
   29. ?>
   
   
2. create a new file in the same directory or folder & name it data.txt and save it.
3. Now run the PHP file.
   enter any text and hit on submit button and check your data.txt file. Your text entered in the form is saved in your text file.

look at the below code. It will also work fine

<?php
             
$data = $_POST['textdata'];
$fp = fopen('data.txt', 'a');
fwrite($fp, $data);
fclose($fp);

this will work fine. But in some servers it might show an error like this

“Undefined index: ”

in order to prevent the error warning we use isset()

so it’s safe to use this before $_POST[‘value’];

something like this one

if(isset($_POST['value'])) { 

       //////your code

 }