Category: mysql

Posts related to MySQL code and database configuration.

Posted on

MySQL Date and Time Functions 5.7

In the world of database management, dates and times play a pivotal role in organizing and retrieving data effectively. MySQL, a widely used relational database management system, offers a robust set of date and time functions to simplify the manipulation and analysis of temporal data. Whether you’re calculating time differences, formatting dates for display, or converting time zones, MySQL’s comprehensive suite of date functions has you covered. In this blog post, we’ll delve into a diverse range of MySQL date and time functions, exploring their functionalities and providing real-world examples to showcase how they can elevate your database queries and applications.

MySQL date and time functions

ADDDATE(): Adds a specified interval to a date and returns the resulting date.

ADDTIME(): Adds a specified time interval to a time and returns the resulting time.

CONVERT_TZ(): Converts a datetime value from one time zone to another time zone.

CURDATE(): Returns the current date.

CURRENT_DATE() / CURRENT_DATE: Synonyms for CURDATE(), returning the current date.

CURRENT_TIME() / CURRENT_TIME: Returns the current time.

CURRENT_TIMESTAMP() / CURRENT_TIMESTAMP: Returns the current date and time.

CURTIME(): Returns the current time.

DATE_ADD(): Adds a specified time interval to a date and returns the resulting date.

DATE_FORMAT(): Formats a date or datetime value as a string using a specified format.

DATE_SUB(): Subtracts a specified time interval from a date and returns the resulting date.

DATE(): Extracts the date part from a datetime value.

DATEDIFF(): Calculates the difference in days between two dates.

DAY(): Extracts the day of the month from a date.

DAYNAME(): Returns the name of the day for a given date.

DAYOFMONTH(): Synonym for DAY(), extracting the day of the month from a date.

DAYOFWEEK(): Returns the index of the day of the week (1 = Sunday, 2 = Monday, etc.) for a given date.

DAYOFYEAR(): Returns the day of the year for a given date.

EXTRACT(): Extracts a specified part (year, month, day, etc.) from a date.

FROM_DAYS(): Converts a day number to a date.

FROM_UNIXTIME(): Converts a Unix timestamp to a date.

GET_FORMAT(): Returns a date format string for a specified combination of date and time parts.

HOUR(): Extracts the hour from a time.

LAST_DAY(): Returns the last day of the month for a given date.

LOCALTIME() / LOCALTIME: Synonyms for CURRENT_TIME(), returning the current time.

LOCALTIMESTAMP() / LOCALTIMESTAMP: Synonyms for CURRENT_TIMESTAMP(), returning the current date and time.

MAKEDATE(): Creates a date from a year and day-of-year value.

MAKETIME(): Creates a time from hour, minute, second values.

MICROSECOND(): Extracts the microseconds part from a time or datetime.

MINUTE(): Extracts the minutes part from a time.

MONTH(): Extracts the month from a date.

MONTHNAME(): Returns the name of the month for a given date.

NOW(): Returns the current date and time.

PERIOD_ADD(): Adds a specified number of months to a period.

PERIOD_DIFF(): Calculates the difference in months between two periods.

QUARTER(): Returns the quarter of the year for a given date.

SEC_TO_TIME(): Converts seconds to a time value.

SECOND(): Extracts the seconds part from a time.

STR_TO_DATE(): Converts a string to a date using a specified format.

SUBDATE(): Synonym for DATE_SUB(), subtracting a specified time interval from a date.

SUBTIME(): Subtracts a specified time interval from a time and returns the resulting time.

SYSDATE(): Returns the current date and time.

TIME_FORMAT(): Formats a time or datetime value as a string using a specified format.

TIME_TO_SEC(): Converts a time value to seconds.

TIME(): Extracts the time part from a datetime.

TIMEDIFF(): Calculates the difference between two time or datetime values.

TIMESTAMP(): Converts a date and time to a datetime value.

TIMESTAMPADD(): Adds a specified time interval to a datetime and returns the resulting datetime.

TIMESTAMPDIFF(): Calculates the difference between two datetimes in a specified unit.

TO_DAYS(): Converts a date to a day number.

TO_SECONDS(): Converts a datetime to seconds since year 0.

UNIX_TIMESTAMP(): Returns the current Unix timestamp.

UTC_DATE(): Returns the current UTC date.

UTC_TIME(): Returns the current UTC time.

UTC_TIMESTAMP(): Returns the current UTC date and time.

WEEK(): Returns the week number for a given date.

WEEKDAY(): Returns the index of the day of the week (0 = Monday, 1 = Tuesday, etc.) for a given date.

WEEKOFYEAR(): Returns the week number of the year for a given date.

YEAR(): Extracts the year from a date.

YEARWEEK(): Returns the year and week number for a given date.

MySQL date functions serve as invaluable tools for handling date and time-related tasks within your database operations. By harnessing the power of functions like DATE_FORMAT(), TIMESTAMPDIFF(), and CONVERT_TZ(), you can transform raw temporal data into meaningful insights, improve user experiences through intelligently formatted displays, and ensure accurate computations across various time zones. The flexibility and precision that these functions offer empower developers to tackle complex temporal challenges with ease, ultimately enhancing the efficiency and accuracy of their applications. As you continue your journey in mastering MySQL, a solid understanding of these date functions will undoubtedly elevate your proficiency and proficiency in database management.

Posted on

How to use MySQL exit and quit and \q commands with examples

In this post I’m going to quickly explain how to use MySQL exit and quit and \q commands to return to the operating system’s command line shell.

When working with MySQL at the command line you’ll arrive at a point when you are finished working.  Thus, you’ll want to exit mysql and return back to your operating system’s command shell.  MySQL provides two keywords for doing this, namely, exit and quit as well as a shorthand version \q.

So for example, say you’re sitting at the mysql command line and it looks like this…

mysql>

And you wish to go back to your command shell…

mysql>exit

-OR-

mysql>quit

(Type either exit or quit and press enter)

You can also use some shorthand to exit or quit the command line.  You can use the \q to quit as well.

It looks like this…

mysql>\q

(Type \q and press enter)

It’s as simple as that.  Learning how to use the MySQL command line can be powerful and efficient.  These three commands will help you on your way to using the command line more effectively.

 

Posted on

How to Declare and Use the MySQL DECIMAL Data Type

How to declare and use MySQL DECIMAL

What is a MySQL DECIMAL?  Simply put, it is just a number that can have a decimal in it (2012.56 and -2013.0034 are examples).  Let’s take a look at how to declare a MySQL DECIMAL.

DECIMAL(M, D)

M is the maximum number of digits (IE precision) and has a range from 1 to 65 (Take note that versions of MySQL prior to 5.0.3 allowed the range from 1 to 254).  This is the total number of digits INCLUDING decimal digits.

D is the number of digits to the right of the decimal point (IE scale) and has a range from 0 to 30 AND cannot be larger than M.

Why use a MySQL DECIMAL?

You would generally use a decimal type when you need to store exact fractional values like money.  It doesn’t suffer from the rounding errors of other number types in MySQL.

How do you use MySQL DECIMAL?

Here is some example mysql code…

CREATE DATABASE IF NOT EXISTS Testing;
USE Testing;
CREATE TABLE WeightCalculation (WeightCalculationID INT NOT NULL, Weight DECIMAL(10,5) NOT NULL);
INSERT INTO WeightCalculation (WeightCalculationID, Weight) VALUES (1,54.3445);
INSERT INTO WeightCalculation (WeightCalculationID, Weight) VALUES (2,928.23017);
SET @a = (SELECT SUM(WEIGHT) FROM WeightCalculation);
INSERT INTO WeightCalculation (WeightCalculationID, Weight) VALUES (3,@a);
SELECT * FROM WeightCalculation;
DROP DATABASE Testing;

Results

WeightCalculationID|Weight
1	54.34450
2	928.23017
3	982.57467

So let walk through this code and see what is going on.  To start, I create our Testing database if it doesn’t exist.  Then I create a table called ‘WeightCalculation’ and give it two columns ‘WeightCalculationID’ and ‘Weight’ in order to store some simple data.  Notice that column ‘Weight’ has been declare a DECIMAL of total length 10 digits and up to 5 decimal places.  Next, I insert two rows of data into the table.  Just for fun I calculate the sum of all the Weight column values and store them in a user-defined variable named @a then insert the sum into another row of the table.  Lastly, I perform a SELECT operation on the table to get it’s contents.  To clean up I drop the Testing database.

For further information check here.  Check here for more examples of precision math.

How to declare and use MySQL DECIMAL
How to declare and use MySQL DECIMAL