How to learn Strings and datetime Analytic functions in MySql for data science?

by · Published · Updated

Data Science is in too much Hype these days. Every organization is now a day looking for data scientists. The Data science role includes the collection, retrieval, analyzing, and representation of data in pictorial format, including statistical information to the users.

The need for data scientists has been rise due to an increase in the use of the internet. Every layman use data these days. Which causes an increase in data in zillions and trillions.

So, here are some most useful and important MySql functions.

  • String functions
    • Lower and Upper
    • Concat
    • Trim
    • Ltrim and Rtrim
    • Mid
    • length
    • Substring
    • Replace
  • Date functions
    • Date and Time
    • Extract
    • Day
    • Current_timestamp
    • Date_format

String functions

In the database when we are creating tables. We will save string data in most of the columns. These functions help us in formatting the string values easily. We can represent data in a more presentable and readable format to the users/clients.

They are also known as TEXT MINING functions.

Lower and Upper

We can convert the string values to uppercase or lowercase by using the upper() or lower() functions respectively. This helps in representing data with more consistency and formating to the record values.

Concat

Concat() function helps to join two or more strings/columns into one. We just have to pass the strings as arguments which we want to concatenate.

Trim

Trim

Trim() is a very important function not just in MySql, but in any language there is. It is one of the most important string functions. It removes any leading or trailing whitespace from the string. For example, in our sample table, there are many trailing and leading whitespaces in the Lastname column. We can remove these using the trim()  function.

Ltrim and Rtrim

We can trim the blank space from the right side or the trail of the string by using rtrim() function and left side or leading blank space by applying ltrim() function. See the below screen for reference.

Mid

Mid() function helps in fetching some parts of the arguments string. In our dataset, we have client _firstname and client_lastname. There might be a condition where we have to fetch the two initial letters of the last name. Then we can use this function. You can refer to the below screen to see the same working in our dataset.

Substring() also helps in fetching some parts of the string. It is similar to the MID() function in MySql.

In the above example, we have got one initial letter for the name ‘Arnav Kumar’. It is because of the leading blank space in the column value. So, We will be using the trim() function to remove the space in the above query and we will get the below output. And this is what is needed.

length

length() function helps in getting the length of the specified column name of the table. We can simply get the length of client_firstname in the below example.

Replace

Replace()  function is also important part of MySql. It’s very easy to use and can be applied easily.

Suppose we have to replace the city_name of client from ‘Delhi’ to ‘New Delhi’. Then we can achieve the same by using this function. See the below screen for its working.

Date and Time functions

Date and time features are important columns in almost every business. We might need a total sales amount by day or by the hour. These needs vary as per the demand. We will be focusing on some date and time functions to master our analysis skills.

Date and Time

If you have a common column for date and time in the dataset, as We have in the sample table, then we will need to use the date() and time() functions to extract the respective values. See the below query to get the date and time values separated from the one column having both values in common.

Extract

There might be a need when we require the only year, month, day, etc values from the column name. Then, we can use the Extract function to get the desired output.

Day

DAY() is similar for dayofmonth(). It will tell us the day of the month dor order_date column. The date can be in the range 1 or 31, or 0 for dates such as ‘0000-00-00’ or ‘2008-00-00’ that have a zero daypart.

Current_timestamp

If you have to store the system generated the current date and time value in the table row. Then, current_timestamp function plays an important role in it.

Date_format

Sometimes the dates in the database will be saved in a different format compared to how you would want to view them. Therefore, to change the date format, you can use the date_format() function. The syntax is as follows: date_format(date, format)

Currently, the dates saved in the sample table are in the year-month-day format. Using this function, I will output the dates in the day-month name-year format.

Endnotes

To summarize, I have covered some of the most commonly used Strings and DateTime functions to be used quite a lot in day-to-day data analysis tasks. I will be adding soon some more articles for reference.

Hope this article helps you bring out more from your dataset. And if you have any favorite MySql function that you find useful or use quite often, do comment below and share your experience!

Do see some important function here for data analysis in Mysql.

Some Useful aggregate and mathematical functions in MYSQL for data science

See all MySQL String functions MySQL 8 String Functions.

See all PHP string functions PHP String Functions.

You may also like...