Nandini Ikhankar’s Post

MySQL - Date and Time Functions In MySQL, we have a set of functions using which we can manipulate the date and time values. Following are the MySQL date time functions − 1. ADDDATE() This function adds two given dates 2.ADDTIME() This function adds given time values 3.CONVERT_TZ() This function converts from one time zone to another 4.CURDATE() This function returns the current date 5.CURRENT_DATE(), CURRENT_DATE Synonyms for CURDATE() 6.CURRENT_TIME(), CURRENT_TIME Synonyms for CURTIME() 7.CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP Synonyms for NOW() 8.CURTIME() This function returns the current time 9.DATE_ADD() Adds two dates 10.DATE_FORMAT() This function formats the given date as specified 11.DATE_SUB() This function subtracts two dates 12.DATE() This function extracts the date part of a date or datetime expression 13DATEDIFF() This function subtracts two dates 14DAY() This function retrieves the day of the month from the given date 15DAYNAME() This function returns the name of the weekday 16DAYOFMONTH() This function returns the day of the month (1-31) 17.DAYOFWEEK() This function returns the weekday index of the argument 18.DAYOFYEAR() This function returns the day of the year (1-366) 19.EXTRACT This function extracts part of a date 20.FROM_DAYS() This function converts a day number to a date 21.FROM_UNIXTIME() This function formats date as a UNIX timestamp 22.HOUR() This function Extracts the hour 23.LAST_DAY This function returns the last day of the month for the argument 24.LOCALTIME(), LOCALTIME Synonym for NOW() 25.LOCALTIMESTAMP, LOCALTIMESTAMP() Synonym for NOW() 26.MAKEDATE() This function creates a date from the year and day of year 27.MAKETIME() This function creates a time value from the given hours, minutes, and seconds. 28MICROSECOND() This function returns the microseconds from argument 29.MINUTE() This function returns the minute from the argument 30.MONTH() This function returns the month from the date passed 31.MONTHNAME() This function returns the name of the month 32.NOW() This function returns the current date and time 33.PERIOD_ADD() This function adds a period to a year-month 34.PERIOD_DIFF() This function returns the number of months between periods 35.QUARTER() This function returns the quarter from a date argument 36.SEC_TO_TIME() This function converts seconds to 'HH:MM:SS' format 37.SECOND() This function returns the second (0-59) 38.STR_TO_DATE() This function converts a string to a date 39.SUBDATE() This function subtracts the specified interval to a date value 40.SUBTIME() This function subtracts the specified time interval to a date time or, time value 41.SYSDATE() This function returns the time at which the function executes 42.TIME_FORMAT() This function formats the given date in the specified format 43TIME_TO_SEC() This function returns the argument converted to seconds 44.TIME() This function extracts the time portion of the expression passed #MYSQL #datacleaning

🚀 Excited to share my latest blog post on Understanding String Functions in MySQL! 🎉 In the world of database management, MySQL's string functions are like magic wands ✨ that help us manipulate and transform text data with ease. Whether you're a seasoned database pro or just dipping your toes into the world of SQL, this comprehensive guide will walk you through everything you need to know about MySQL's string functions. We'll explore a range of functions, from simple tasks like combining words and cutting out unnecessary parts, to more advanced techniques like changing letter cases and extracting specific substrings. Plus, we'll dive into common SQL queries that complement these string functions, giving you a well-rounded understanding of how to work with text data in MySQL. Ready to level up your MySQL skills? Check out the blog post here https://lnkd.in/gCYPtjqj and unlock the secrets of MySQL's string functions today! 💡💻 #MySQL #DatabaseManagement #SQL #StringFunctions #DataManipulation

🚀 Excited to Share My Latest MySQL Exercise Completion! 🚀 I've just wrapped up another set of challenging tasks in MySQL, focusing on retrieving and manipulating employee data. Here’s what I worked on: 🔍 Tasks Completed: 1️⃣ Find employees whose salaries are between 30,000 and 40,000: SELECT * FROM employees WHERE salary BETWEEN 30000 AND 40000; 2️⃣ Find employees whose names start with 'R' or 'S': SELECT * FROM employees WHERE name LIKE 'R%' OR name LIKE 'S%'; 3️⃣ Find employees whose salary is 25,000 and the department is 'Cash': SELECT * FROM employees WHERE salary = 25000 AND dept = 'Cash'; 4️⃣ Find employees from the following designations: Manager, Lead, and Associate: SELECT * FROM employees WHERE design IN ('Manager', 'Lead', 'Associate'); 5️⃣ Convert salary to dollars using a CASE statement and create a new column Sal in dollars: SELECT name, salary, CASE    WHEN salary != 0 THEN salary / 80 END AS 'Sal in dollars' FROM employees; 📊 Output: +---------+--------+----------------+ | name  | salary | Sal in dollars | +---------+--------+----------------+ | Raju  | 37000 |    462.5000 | | Sham  | 32000 |    400.0000 | | Paul  | 45000 |    562.5000 | | Alex  | 35000 |    437.5000 | | Victor | 35000 |    437.5000 | | Leena  | 25000 |    312.5000 | | John  | 75000 |    937.5000 | | Alex  | 40000 |    500.0000 | | Baburao | 25000 |    312.5000 | +---------+--------+----------------+ 💡 Key Takeaway: Learned to efficiently filter data using BETWEEN, LIKE, AND, IN, and manipulate data with CASE statements in MySQL. These skills are crucial for working with large datasets and extracting meaningful insights. Always eager to learn and improve my database skills! 💻✨ #MySQL #SQL #DataManagement #DatabaseQueries #Coding #LearningJourney #DataAnalysis #TechSkills

Resolving Error in MySQL 👀 Hello everyone, Let's say we have an SQL query like this: SELECT e.event_type, ei.event_name, COUNT(*) FROM events e LEFT JOIN event_identifier ei ON e.event_type = ei.event_type GROUP BY e.event_type; When you run this query, you will get an error. Did you figure out why we got this error? If yes, Congratulations, you know how GROUP BY clause works. If not, I will explain you Here we go The error you're encountering is due to SQL's GROUP BY rule: when using GROUP BY, all selected columns must either be included in the GROUP BY clause or be used with an aggregate function (like COUNT, SUM, MAX, etc.) In our query, we are selecting both e.event_type and ei.event_name, but in GROUP BY clause, we are grouping only by e.event_type. So, SQL doesn't know how to handle ei.event_name because it needs to either be aggregated or included in the GROUP BY. ❓Why does this happen SQL groups rows based on the columns listed in the GROUP BY. If you don’t group by ei.event_name, but you try to select it, SQL cannot determine which value of ei.event_name to return for each group of e.event_type ✅Solutions 1. Keep ei.event_name in the GROUP BY clause If you need both e.event_type and ei.event_name in the result, keep them both in the GROUP BY clause 2. Use an aggregate function for ei.event_name If you only want to group by e.event_type, you can apply an aggregate function to ei.event_name. For example, you could use MAX or MIN if you want one representative value from the group. There is on more way which is by changing the settings in MySQL, but it is not recommended. That is: 3. Disable ONLY_FULL_GROUP_BY If you're in a development environment and want to disable ONLY_FULL_GROUP_BY, you can change the SQL mode: SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY','')); Disabling ONLY_FULL_GROUP_BY is not recommended in production environments because it can lead to ambiguous results. The best approach would be to use either method 1 or 2, depending on your specific use case. That's it. #mysql #database #datascience #dataanalytics

🌟✨ Just Revived MySQL Concepts! ✨🌟 I recently revisited some key MySQL concepts and functions, and it was a great refresher! 📚 Here’s what I covered, along with some examples: ✅ Data Types 🧮: 🔘 `DECIMAL(5, 2)` :- Stores a number like `123.45` with fixed precision. 🔘 `FLOAT` & `DOUBLE` :- Useful for approximate values like `3.14159`. 🔘 `DATE`, `TIME`, `DATETIME` 🗓️ :- `2024-10-07`, `14:30:00`, `2024-10-07 14:30:00`. ✅ Date & Time Functions ⏰: 🔘 `CURTIME()` :- Returns the current time, e.g., `14:30:00`. 🔘 `CURDATE()` :- Returns today’s date, e.g., `2024-10-07`. 🔘 `NOW()` :- Returns current date and time, e.g., `2024-10-07 14:30:00`. ✅ Date Information Functions 📅: 🔘 `DAYNAME('2024-10-07')` :- Returns `Monday`. 🔘 `DAYOFMONTH('2024-10-07')` :- Returns `7`. 🔘 `DAYOFWEEK('2024-10-07')` :- Returns `2` (Monday). 🔘 `MONTHNAME('2024-10-07')` :- Returns `October`. 🔘 `HOUR('14:30:00')` :- Returns `14`. 🔘 `MINUTE('14:30:00')` :- Returns `30`. ✅ Formatting Functions 📝: 🔘 `DATE_FORMAT(NOW(), '%D %a at %T')` :- Returns `7th Mon at 14:30:00`. 🔘 `DATE_FORMAT(NOW(), '%m/%d/%y')` :- Returns `10/07/24`. ✅ Date Calculations ➕➖: 🔘 `DATEDIFF('2024-10-07', '2024-09-01')` :- Returns `36` (days). 🔘 `DATE_ADD('2024-10-07', INTERVAL 5 DAY)` :- Adds 5 days, returning `2024-10-12`. 🔘 `DATE_SUB('2024-10-07', INTERVAL 2 MONTH)` :- Subtracts 2 months, returning `2024-08-07`. 🔘 `TIMEDIFF('14:30:00', '12:00:00')` :- Returns `02:30:00`. ✅ Timestamp Management ⏳: 🔘 Using `DEFAULT CURRENT_TIMESTAMP` and `ON UPDATE CURRENT_TIMESTAMP` :- helps in automatically tracking creation and update times for records. Many more functions exist in MySQL, but I’ve highlighted some key ones here. For a deeper understanding, feel free to explore other functions through MySQL documentation or online resources! 🌐 It’s always exciting to deepen my understanding of database management and enhance my skills! 🚀 #MySQL #Database #SQL #LearningJourney #DataManagement #WebDevelopment #Coding #Programming

**🚀 Boost Your MySQL Game with Dynamic Column Selection: No More Tedious Column Listings!** 🙌 Ever found yourself needing to **select all columns EXCEPT one** in MySQL? 🤔 You’re not alone! As developers, we’ve all wished for a simple `SELECT * EXCEPT column_name` syntax, but guess what? **MySQL doesn’t offer it natively**. No worries though—I’ve got a slick workaround that’s both **elegant** and **dynamic**, leveraging `INFORMATION_SCHEMA.COLUMNS` and MySQL prepared statements. 💡 Let's dive into it! **🛠 Step-by-Step Guide:** ### **Step 1:** Grab All the Columns Dynamically (Minus the One You Don’t Need) Instead of manually listing every column, we can use this clever query to pull all the column names from a table **except** the one you want to exclude. sql SELECT GROUP_CONCAT(COLUMN_NAME) INTO @columns FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'driver'   AND TABLE_SCHEMA = 'test'  AND COLUMN_NAME != 'name'; -- Exclude 'name' column This magic uses `GROUP_CONCAT` to get a comma-separated list of column names, excluding the one you don’t need. 🎩✨ ### **Step 2:** Construct and Execute the Query Dynamically Now that we have our list of columns, let’s dynamically build the `SELECT` statement and run it using MySQL prepared statements: sql SET @query = CONCAT('SELECT ', @columns, ' FROM driver'); PREPARE stmt FROM @query; EXECUTE stmt; DEALLOCATE PREPARE stmt; And just like that—you’ve got all columns except the one you don’t want in your result! 💥 No manual listing, no problem. **🔥 Why This Approach Rocks:** - **Automates** column selection, so you don’t have to hard-code them. - Perfect for large tables where manually listing columns would be a nightmare. 😱 - **Adaptable** to schema changes—you only exclude what you need, and the rest updates automatically. **🎯 Pro Tip:** Want to save time across multiple tables? Wrap this logic into a **stored procedure**! You’ll be able to reuse it effortlessly whenever you need it. **💬 Your Turn!** Have you faced the same challenge with column exclusions in SQL? Let’s talk about how you handle these kinds of queries! Drop a comment with your thoughts or any other creative SQL tricks you've got. I’d love to hear from you! 👇 #MySQL #DynamicSQL #SQLTips #DatabaseMagic #DataEngineering #FullStackDev #CodingTricks #ProgrammingHacks

𝐐𝐮𝐞𝐫𝐲 𝐎𝐩𝐭𝐢𝐦𝐢𝐳𝐚𝐭𝐢𝐨𝐧 𝐔𝐬𝐢𝐧𝐠 𝐄𝐗𝐏𝐋𝐀𝐈𝐍 There are many techniques in MySQL to improve query performance, and one of the most effective tools is the EXPLAIN command. In this post, we will look at how to use this tool to optimize queries. The EXPLAIN command in MySQL provides information about how MySQL plans to execute a query. It allows you to understand what indexes are being used, how many rows will be read, and what operations will be performed. Using EXPLAIN helps you identify bottlenecks and optimize queries to improve performance. 𝐁𝐚𝐬𝐢𝐜 𝐟𝐢𝐞𝐥𝐝𝐬 𝐢𝐧 𝐄𝐗𝐏𝐋𝐀𝐈𝐍 𝐨𝐮𝐭𝐩𝐮𝐭 When you execute the EXPLAIN command, MySQL returns a table with various fields, each of which provides important information about the query execution plan: - id: Query identifier. - select_type: The type of query (for example, SIMPLE, PRIMARY, UNION). - table: The table to which the query applies. - type: Connection type (e.g. ALL, index, range, ref, eq_ref, const, system). - possible_keys: Possible indexes that can be used. - key: The index that is actually used. - rows: The estimated number of rows that will be read. - Extra: Additional information (e.g., Using index, Using where). 𝐒𝐭𝐞𝐩𝐬 𝐭𝐨 𝐨𝐩𝐭𝐢𝐦𝐢𝐳𝐞 𝐪𝐮𝐞𝐫𝐢𝐞𝐬 𝐮𝐬𝐢𝐧𝐠 𝐄𝐗𝐏𝐋𝐀𝐈𝐍 - Analyze a query using EXPLAIN: Use the EXPLAIN command to analyze the current query execution plan. Pay attention to the `type`, `possible_keys`, `key`, and `rows` fields. This will help you understand how MySQL processes the query and where the bottlenecks may be. - Creating and using indexes: If EXPLAIN shows that indexes are not being used, create them for the appropriate columns. For example, if a query frequently filters data on a particular column, create an index for that column. This can significantly speed up query execution. - Optimize complex queries: Rewrite complex queries to reduce the number of I/O operations. For example, use subqueries or joins (JOIN) instead of nested queries. This can help MySQL process queries more efficiently. - Result caching: For frequently executed queries, use result caching to reduce the load on the database. This is especially useful for queries that are executed repeatedly with the same parameters. 𝐂𝐨𝐧𝐜𝐥𝐮𝐬𝐢𝐨𝐧 Optimizing queries in MySQL using the EXPLAIN command is a powerful tool for improving database performance. Proper use of this method can significantly reduce query execution time and reduce the load on the system. Don't forget to analyze and optimize your queries regularly to keep your database performance high.

CONCAT() vs CONCAT_WS() 🤔 In MySQL, both `CONCAT()` and `CONCAT_WS()` are functions used to concatenate strings, but they have some key differences in how they handle their inputs and outputs: CONCAT() 🔸CONCAT(string1, string2, ..., stringN) 🔸It simply concatenates all the given strings in the order they are provided. If any argument is `NULL`, the result is `NULL`. ✏Example: SELECT CONCAT('Hello', ' ', 'World'); o|p: Hello World ⭕If any value is `NULL`: SELECT CONCAT('Hello', NULL, 'World'); o|p: NULL CONCAT_WS() 🔸The CONCAT_WS() function adds two or more expressions together with a separator. 🔸CONCAT_WS(separator, string1, string2, ..., stringN) 🔸Unlike `CONCAT()`, `CONCAT_WS()` skips any `NULL` values instead of returning `NULL`. ✏Example: SELECT CONCAT_WS(', ', 'Hello', 'World', 'MySQL'); o|p: Hello, World, MySQL ⭕If any value is `NULL`: SELECT CONCAT_WS(', ', 'Hello', NULL, 'World'); o|p: 'Hello, World' (The `NULL` value is ignored.) ➡ Key Differences in Simple Terms 1. Separator:   - `CONCAT()`: Does not use a separator between the strings.   - `CONCAT_WS()`: Uses a separator (specified as the first argument) between the strings. 2. Handling `NULL` values:   - `CONCAT()`: If any argument is `NULL`, the result is `NULL`.   - `CONCAT_WS()`: Skips `NULL` values and still concatenates the non-`NULL` strings with the specified separator. ⏺ In summary, use `CONCAT()` when you want to join strings without a separator and are okay with a `NULL` result if any part is `NULL`. Use `CONCAT_WS()` when you want to join strings with a specific separator and want to ignore `NULL` values. #SQL #DataManagement #DataAnalysis #StringFunctions #TechTips #ContinuousLearning