How to convert julian date to normal date in MySQL

In this article, we will learn how to convert a Julian date to the calendar date with MySQL.

First, let’s see how it looks like a date in Julian format.

Julian calendar and Julian date format

The Julian calendar was used since the time of Julio Cesar, specifically 45 years before Christ. It was used until 1582 when Pope Gregory XIII introduced the Gregorian calendar that we currently use.

The dates in the Julian calendar can have several formats but usually, the first digits indicate the year and the last ones indicate the days that have elapsed in that year. In this article, we will use the following format: YYYYDDD.

Where YYYY indicates the year, and DDD indicates the number of days elapsed in that year.

Let’s see some examples:

Table 1. Julian date conversion examples

MySQL julian date conversion

To illustrate our example, we will use an Orders table with the following structure and data:

CREATE TABLE `orders_table` (   `id` int(11),   `client_id` int(11),   `date_received` integer,   `date_processed` integer,   `total_amount` float,   `total_paid` float ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `orders_table` (`id`, `client_id`, `date_received`, `date_processed`, `total_amount`, `total_paid`) VALUES (1, 1, 2010079, 2010082, 100, 75); INSERT INTO `orders_table` (`id`, `client_id`, `date_received`, `date_processed`, `total_amount`, `total_paid`) VALUES (2, 1, 2011110, 2011112, 50.2, 20.5); INSERT INTO `orders_table` (`id`, `client_id`, `date_received`, `date_processed`, `total_amount`, `total_paid`) VALUES (3, 2, 2012125, 2012126, 75, 60); INSERT INTO `orders_table` (`id`, `client_id`, `date_received`, `date_processed`, `total_amount`, `total_paid`) VALUES (4, 2, 2013200, 2013200, 90.7, 20); INSERT INTO `orders_table` (`id`, `client_id`, `date_received`, `date_processed`, `total_amount`, `total_paid`) VALUES (5, 3, 2014225, 2014228, 120, 100); INSERT INTO `orders_table` (`id`, `client_id`, `date_received`, `date_processed`, `total_amount`, `total_paid`) VALUES (6, 1, 2015052, 2015054, 500, NULL); INSERT INTO `orders_table` (`id`, `client_id`, `date_received`, `date_processed`, `total_amount`, `total_paid`) VALUES (7, 1, 2016123, 2016124, 150, 150); INSERT INTO `orders_table` (`id`, `client_id`, `date_received`, `date_processed`, `total_amount`, `total_paid`) VALUES (8, 3, 2017312, 2017312, 200, 50); INSERT INTO `orders_table` (`id`, `client_id`, `date_received`, `date_processed`, `total_amount`, `total_paid`) VALUES (9, 2, 2018224, 2018227, 250, 250); INSERT INTO `orders_table` (`id`, `client_id`, `date_received`, `date_processed`, `total_amount`, `total_paid`) VALUES (10, 3, 2019360, 2019363, 300, 0); ALTER TABLE `orders_table` ADD PRIMARY KEY (`id`);

Script 1. Orders table creation

We can execute the following command to verify the contents of the Orders table:

SELECT * FROM `orders_table`

Script 2. Query to retrieve Orders table content

Result:

Table 2. Orders table content

As we can see, the received and processed fields have saved the dates in Julian YYYYDDD format.

Let’s build a query to convert the dates of the received field from Julian to normal Calendar:

SELECT `id`, `client_id`, `date_received` as `Received Julian`, MAKEDATE(LEFT(`date_received`,4) , `date_received` % 1000) as `Received Normal Calendar`, `total_amount`, `total_paid` FROM `orders_table`

Script 3. Query to convert julian date to calendar date

To build our query, we rely on the LEFT function to obtain the four digits of the year and the % operator to calculate the elapsed days.

Result:

Table 3. Received field with a new format 

Comparing Tables and Columns in MySQL

In MySQL, we can compare tables and columns at the record level by looking at their values side by side. We will use MySQL JOIN clauses to do this. In this article, we look at the different JOIN operators through various examples.

As programmers, when building an application, we often find problems or conditions that require us to compare records in the database table. On this occasion, we will discuss the query of how to compare tables in MySQL tables and columns.

When comparing and displaying data stored from a database, we will often take data from several tables to be processed so that it becomes a single output. This makes the comparison easier and more efficient.

In most RDBMS including MySQL, this form of data retrieval can be done easily through established table relationships. This form of the relationship varies: we can have a one-to-one relationship, one-to-many, and many-to-many.

Compare 2 Tables in MySQL – Examples

Before going any further, we need to prepare a table that we will use in this article.

In this example, we use four tables, namely: customer table, product table, purchase table, and detail_transaction table. The structure and relationship of the four tables are as follows:

customer table

product table

transaction table

transaction_detail table

The relationships between the tables are described as follows:

Image 1: Relations Between Tables in The Sales Database

Image 2: Data Samples of Product Table

Image 3: Data Samples of Costumer Table

Image 4: Data Samples of Purchase Table

Comparing 2 Tables in MySQL Using the JOIN Statement

To combine and compare 2 tables in MySQL, we use the JOIN statement. In this case, we use several types of JOIN, namely INNER JOIN, LEFT OUTER JOIN, and RIGHT OUTER JOIN. The writing format is as follows:

1. SELECT table_name.column_name
3. FROM table_name
5. INNER JOIN | LEFT OUTER JOIN | RIGHT OUTER JOIN table_name ON condition

In addition to using the ON statement to define conditions, we can use the USING statement, the writing format is as follows:

1. SELECT table_name.column_name
3. FROM table_name
5. INNER JOIN | LEFT OUTER JOIN | RIGHT OUTER JOIN USING table_name (column_name)

This USING statement will use column names in parentheses to connect the two tables. This column must be in the table that you want to connect to and must have the same name.

Usually, related columns are defined as Primary Keys and Foreign Keys. This is part of database design best practice, but not a hard requirement.

Example 1: INNER JOIN Statement

The first way to combine tables is to use the INNER JOIN. With INNER JOIN, the table will be combined based on the same data, which is in both tables. If it is drawn in the form of a Venn diagram, the INNER JOIN form is shown in the following figure:

Image 5: Venn Diagram of INNER JOIN

In MySQL, writing an INNER JOIN can be done using the INNER JOIN clause, or just a JOIN clause. For example, we will display the data of the customer who made the purchase orders, the query that we run:

1. SELECT customer.id_customer,customer.name,purchase.date_purchase, purchase.total_purchase
3. FROM customer
5. INNER JOIN purchase ON purchase.id_customer = customer.id_customer;

The results will appear as below :

Image 6: The Example Result Of Run INNER JOINT Query

Example 2: OUTER JOIN Statement

The second way to combine tables in MySQL is to use the OUTER JOIN. In the OUTER  JOIN, all data in one table will be displayed, while the data in another table will only be displayed if the data is in the first table.

In MySQL, the OUTER JOIN has two types, namely the LEFT OUTER JOIN and RIGHT OUTER JOIN. In LEFT OUTER JOIN, all data in the left table will be displayed, while the data in the table on the right will only be displayed if the related data in the table appears in the table on the left.

If in the form of a Venn diagram, the LEFT OUTER JOIN form looks like the following example:

Image 7: Venn Diagram of LEFT OUTER JOIN

Example of using LEFT OUTER JOIN:

1. SELECT customer.id_customer,customer.name,purchase.date_purchase, purchase.total_purchase
3. FROM customer
5. LEFT OUTER JOIN purchase ON purchase.id_customer = customer.id_customer;

The results will appear as below:

Image 8: The Example Result Of Run LEFT OUTER JOIN Query

In the example above, we can see that with LEFT OUTER JOIN, the data on the left table (customer table) will all be displayed, while the data on the right table (purchase table) will only be displayed if the id_customer column values ​​appear in the id_costumer in the customer table.

Using JOIN statements allows us to compare the record values for each table side-by-side.

How to compare two columns in MySQL

In MySQL, we can compare two columns  to identify the records they have in common and also, the records one table has but the other does not.

In this article, we will learn how to compare two columns to find common records, and we will also find their differences.

To illustrate the examples in this article, we are going to create two tables (contacts and clients) with the following structure and data:

1. CREATE TABLE `clients` (
2. `id` bigint(20) UNSIGNED NOT NULL,
3. `name` varchar(255) DEFAULT NULL,
4. `address` text
5. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `clients` (`id`, `name`, `address`) VALUES
6. (1, 'Mark Johnson', '2113 Jackson Street,Apt. B,Erie,CA,510'),
7. (2, 'Sasha Smith', '123 Main Street,,Erie,CA,512'),
8. (3, 'Charles Walls', '979 Drummond Street, Newark'),
9. (4, 'Susan Lee', '1572 Hanover Street'),
10. (5, 'Carol Parker', '4834 Blackwell Street');
11. CREATE TABLE `contacts` (
12. `id` int(11) NOT NULL,
13. `name` varchar(20) NOT NULL,
14. `phone` varchar(20) NOT NULL,
15. `email` varchar(20) NOT NULL
16. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
18. INSERT INTO `contacts` (`id`, `name`, `phone`, `email`) VALUES
19. (1, 'John Grey', '19005264555', 'john@gmail.com'),
20. (2, 'Mary Rose', '19008504587', 'mary@gmail.com'),
21. (3, 'Matt Crow', '19006238747', 'matt@gmail.com'),
22. (4, 'Susan Lee', '19006781563', 'susan@gmail.com'),
23. (5, 'Carol Parker', '19007426336', 'carol@gmail.com'),
24. (6, 'Mark Johnson', '19008881234', 'mark@gmail.com'),
25. (7, 'Sasha Smith', '19001527788', 'sasha@gmail.com'),
26. (8, 'Isaac Peterson', '19008795678', 'isaac@gmail.com'),
27. (9, 'Ana Brown', '19006961425', 'ana@gmail.com'),
28. (10, 'Alex Robertson', '19003693791', 'alex@gmail.com');

We can execute the following queries to verify the content of each table:

1. SELECT * FROM `clients`

Result:

Clients table content

1. SELECT * FROM `contacts`

Result:

Contacts table content

Compare two columns in sql

Let’s compare the “NAME” column of both tables to find the names they have in common, and the names that one table has and the other does not.

To find the names that both tables have in common, we will execute the following query:

1. SELECT a.name
2. FROM (SELECT distinct b.name FROM `contacts` b UNION ALL
3. SELECT distinct c.name FROM `clients` c) a
4. GROUP BY a.name
5. HAVING count(*) = 2
6. ORDER BY a.name

Result:

Common names between both tables

As we can see, there are four names in common between both tables. The logic of the query we use to find them is very simple.

First, we request the names in the contacts table, to avoid repeated names we included the DISTINCT clause. Then we made a union with the names in the clients table. Finally, we filter the names that have two occurrences, that is, the names that are in the two tables.

Now, to calculate the differences between the two name columns, we can execute a very similar query, but we will filter the names that have a single occurrence, that is, the names that are in only one table:

1. SELECT a.name
2. FROM (SELECT distinct b.name FROM `contacts` b UNION ALL
3. SELECT distinct c.name FROM `clients` c) a
4. GROUP BY a.name
5. HAVING count(*) = 1
6. ORDER BY a.name

Result:

Name differences

As we can see, there are seven names that are not common between the two tables.