Soothingday

In MySQL, there are many situations that we need to use date functions, not only the transformation from timestamp to date, but also the comparision between different dates. Therefore, it is necessary for me to records some common date functions and consolidate my knowledge in this apsect.

Before starting this article, I would like to say some differences among three general time type in MySQL, date, datetime and timestamp.

1. The format of date type is “YYYY-MM-DD”, so the date type can be accurate to ‘date’. In contrast, datetime type and timestamp type can be accurate to second.
2. The range that timestamp covers is from 1970-01-01 00:00:01 to 2038-01-19 03:14:07. And the range for datetime type is more larger than for timestamp. It can express the time from 1000-01-01 00:00:00 to 9999-12-31 23:59:59.
3. The time in timestamp can be converted automatically based on the timezone of the database, because in database it stores as UTC time zone, and when getting it from database, it will be converted based on UTC. The time in datetime is stable and cannot change as the time zone changes.
4. Timestamp needs to use 4 bytes to store, datetime needs 8 bytes, and date uses 3 bytes.

This article is mainly to record some of my summary about data in my first job.

Why we need the data warehouse

When the scale of data is relatively small, database is a general way to store and read the data. Common relational databases include MySQL, SQL Server and PostgreSQL. However, as the data grows, there will be some bottlenecks when using the database.

1. As the volume of data increases, the efficiency of query is gradually slow.
2. There are too many tables when executing the query, and due to the constraint for the normal form, in many queries we need to do at least one time join operation, which is time-consuming.
3. There may be exist some illegal fields in the table, which would affect the query result

When meeting these bottlenecks, we can consider to use data warehouse to solve these problems. In my first job I got in touch with offline data warehouse, so ‘data warehouse’ means the offline data warehouse if not have other specific explanations.

One of the difference between offline data warehouse and database is that database should support the real time CRUD operation simultaneously, other than offline data warehouse mainly focus on the data reading. Because data warehouse mainly stores the historical data, in real environment, the historical data would be written into data warehouse one or two times, or several days one time. It causes that data warehouse doesn’t have too much requirement for the timeliness. (In general the data are stored in data warehouse in the early morning and can use the tools like Airflow to do the schedule) Therefore, the design for the data warehouse tends to the efficiency of reading data.

I used to think that AND and OR operator were only used for judging True or False, but after reading the book Learn Python the hard way, I found one common question in Boolean Practice.

Why does “test” and “test” return “test” or 1 and 1 return 1 instead of True?

This question aroused my interest, can this two operators return other type value?

Therefore, I looked for some documents on the Internet, and noticed that the calculation logic for AND and OR operator.