Facebook  Youtube 
Newsfeeds
Planet MySQL
Planet MySQL - https://planet.mysql.com

  • SQL Date Functions Tutorial With Example | Date and Time in SQL
    SQL Date Functions Tutorial With Example | Date and Time in SQL is today’s topic. In this section, we cover standard date functions in SQL. The different database system has different formats for date type data, and each RDBMS may employ different date functions, and there may also be differences in the syntax for each RDBMS even when the function call is same. SQL Date Functions Tutorial With Example MySQL comes with the following data types for storing a date or a date/time value in the database: DATE – format YYYY-MM-DD DATETIME – format: YYYY-MM-DD HH:MI:SS TIMESTAMP – format: YYYY-MM-DD HH:MI:SS YEAR – format YYYY or YY LIST OF SQL DATE FUNCTIONS #ADDDATE() It returns a date after a certain time/date interval has been added. select adddate("2019-06-09 02:52:47","7"); See the output.     #ADDTIME() It returns a time/date time after a certain time interval has been added. select addtime("2019-06-09 02:52:47","2"); See the Output.     #CURDATE() It returns the current date. select curdate(); See the output.   #CURRENT_DATE() It returns the current date. select current_date(); See the below output.   #CURRENT_TIME() It returns the current time. select current_time(); See the below output.   #CURRENT_TIMESTAMP() It returns the current date and time. select current_timestamp(); See the output.   #CURTIME()  It returns the current time. select curtime(); See the output.   #DATE() It extracts the date value from a date-time expression. select date("2019-06-10"); See the output.   #DATEDIFF()  It returns the difference in days between two date values. select datediff("2019-06-16","2019-06-10"); See the output.   #DATE_ADD()  It returns a date after a certain time/date interval has been added. select date_add("2019-06-05", INTERVAL 10 DAY); See the following output.   #DATE_FORMAT()  It formats a date as specified by a format mask. select DATE_FORMAT("2018-06-15", "%Y"); See the following output.   #DATE_SUB() It returns a date after a certain time/date interval has been subtracted. SELECT DATE_SUB("2017-06-15", INTERVAL 10 DAY); See the following output.   #DAY() It returns the day portion of a date value. SELECT DAY("2019-07-15"); See the output.   #DAYNAME()  It returns the weekday name for a date. SELECT DAYNAME('2008-05-15'); See the output.   #DAYOFMONTH() It returns the day portion of a date value. SELECT DAYOFMONTH('2018-07-16'); See the output.   #DAYWEEK() It returns the weekday index for a date value. SELECT WEEKDAY("2019-07-16"); See the output.   #DAYOFYEAR() It returns the day of the year for a date value. SELECT DAYOFYEAR("2019-07-16"); See the output.   #EXTRACT()  It extracts parts from a date. SELECT EXTRACT(MONTH FROM "2018-07-16"); See the output.   #FROM_DAYS() It returns a date value from a numeric representation of the day. select from_days(1234567); See the output.   #HOUR() It returns the hour portion of a date value. select hour("2019-01-16 09:25:27"); See the output.   #LAST_DAY() It returns the last day of the month for a given date. select last_day('2019-01-25'); See the output.   #LOCALTIME() It returns the current date and time. select localtime(); See the output.     #LOCALTIMESTAMP() It returns the current date and time. select localtimestamp(); See the output.     #MAKEDATE() It returns the date for a particular year. select makedate(2007,128); See the output.   #MAKETIME() It returns the time for a particular hour, minute, second combination. select maketime(10,25,4); See the output.   #MICROSECOND() It returns the microsecond portion of a date value. select microsecond("2019-06-19 09:10:45.000245"); See the output.   #MINUTE() It returns the minute portion of a date value. select minute("2019-08-20 09:12:00"); See the output.   #MONTH() It returns the month portion of a date value. select month('2019/01/15'); See the output.   #MONTHNAME()  It returns the full month name for a date. select monthname('2019/1/16'); See the output.   #NOW()  It returns the current date and time. select now(); See the output.   #PERIOD_ADD()  It takes a period and adds a specified number of months to it. select period_add(201803, 6); See the output.   #PERIOD_DIFF() It returns the difference in months between two periods. SELECT PERIOD_DIFF(201810, 201802); See the output.   #QUARTER() It returns the quarter portion of a date value. SELECT QUARTER("2018/07/18"); See the output.   #SECOND() It returns the second portion of a date value. SELECT SECOND("09:14:00:00032"); See the output.   #SEC_TO_TIME() It converts numeric seconds into a time value. SELECT SEC_TO_TIME(1); See the output.   #STR_TO_DATE() It takes a string and returns a date specified by a format mask. SELECT STR_TO_DATE("JULY 18 2019", "%M %D %Y"); See the output.   #SUBDATE()  It returns a date after which a certain time/date interval has been subtracted. SELECT SUBDATE("2019-06-15", INTERVAL 10 DAY); See the output.   #SYSDATE()  It returns the current date and time. SELECT SYSDATE(); See the output.   #TIME() It extracts the time value from a time/date time expression. SELECT TIME("09:16:10"); See the output.   #TIME_FORMAT() It formats the time as specified by a format mask. SELECT TIME_FORMAT("09:16:10", "%H %I %S"); See the output.   #TIME_TO_SEC()  It converts a time value into numeric seconds. SELECT TIME_TO_SEC("09:16:10"); See the output.   #TIMEDIFF() It returns the difference between two time/datetime values. SELECT TIMEDIFF("09:16:10", "09:16:04"); See the output.   #TIMESTAMP()  It converts an expression to a date-time value and if specified, adds an optional time interval to the value. SELECT TIMESTAMP("2019-06-10", "08:16:10"); See the output.   #TO_DAYS() It converts a date into numeric days. SELECT TO_DAYS("2018-07-18"); See the output.   #WEEK() It returns the week portion of a date value. SELECT WEEK("2018-06-18"); See the output.   #WEEKDAY() It returns the weekday index for a date value. SELECT WEEKDAY("2018-07-18"); See the output.   #WEEKOFYEAR() It returns the week of the year for a date value. SELECT WEEKOFYEAR("2018-07-18"); See the output.   #YEAR()  It returns the year portion of a date value. SELECT YEAR("2019-07-18"); See the output.   #YEARWEEK() It returns the year and week for a date value. SELECT YEARWEEK("2019-06-18"); See the output.   Finally, SQL Date Functions Tutorial With Example | Date and Time in SQL is over. The post SQL Date Functions Tutorial With Example | Date and Time in SQL appeared first on AppDividend.

  • Using Keep-Alives To Ensure Long-Running MySQL & MariaDB Sessions Stay Connected
    Overview The Skinny In this blog post we will discuss how to use the Tungsten Connector keep-alive feature to ensure long-running MySQL & MariaDB/Percona Server client sessions stay connected in a Tungsten Cluster. Agenda What’s Here? Briefly explore how the Tungsten Connector works Describe the Connector keep-alives – what are they and why do we use them? Discuss why the keep-alive feature is not available in Bridge mode and why Examine how to tune the keep-alive feature in the Tungsten Connector Tungsten Connector: A Primer A Very Brief Summary The Tungsten Connector is an intelligent MySQL database proxy located between the clients and the database servers, providing a single connection point, while routing queries to the database servers. √ High-Availability The most important function of the Connector is failover handling. In the event of a failure, the Tungsten Connector can automatically route queries away from the failed server and towards servers that are still operating. When the cluster detects a failed master because the MySQL server port is no longer reachable, the Connectors are signaled and traffic is re-routed to the newly-elected Master node. √ Read-Scaling Next is the ability to provide read-scaling and route MySQL queries based on various factors. During the routing process, Tungsten Connector communicates with the Tungsten Manager to determine which datasources are the most up to date, and their current role so that the packets can be routed properly. In the default Bridge mode, traffic is routed at the TCP layer, and read-only queries must be directed to a different port (normally 3306 for writes and 3307 for reads). There are additional modes, Proxy/Direct and Proxy/SmartScale. In both cases, queries are intercepted and inspected by the Connector. The decisions made are tunable based on configuration parameters. For more detailed information about how the Tungsten Connector works, please read our blog post, “Experience the Power of the Tungsten Connector” For a comparison of Routing methods, please see the documentation page: http://docs.continuent.com/tungsten-clustering-6.0/connector-routing-types.html Tungsten Connector: Keep-Alives What are they and why do we use them? Connections to MySQL servers can automatically time-out according to the wait_timeout variable configured within the MySQL server. To prevent these connections being automatically closed, the connector can be configured to keep the connection alive by submitting a simple SELECT statement (actually SELECT ‘KEEP_ALIVE’;) periodically to ensure that the MySQL timeout is not reached and the connection closed. The keep-alive feature was designed with Proxy modes in mind (Proxy/Direct and Proxy/SmartScale). When using either, Proxy mode, every single client connection gets 2 mysql server-side connections: one for reads and one for writes. If your application is read-intensive, the server-side read-only connection gets updated often and is kept alive by MySQL. Under those conditions, the write connection is NOT being unused, and so there is a risk the MySQL server’s wait_timeout to expire, so the next write on the client side connection would get an error. In response to the above scenario, the keep-alive feature was implemented. Keep-alives by default are enabled and set to autodetect, which will compute suitable values based on the MySQL server wait_timeout in order to be totally transparent to the application. This design will produce the exact same behavior as if the application were connected directly to the database server. Keep-Alives and Bridge Mode Why They Do Not Work Together The Connector Keep-alive feature is NOT compatible with Bridge mode. In Bridge mode, the client session is directly connected to the MySQL server at the TCP level, literally forwarding the client’s packet to the server. This means that closing connections is the responsibility of the MySQL server based on the configured wait_timeout value, not the Connector. Summary The Wrap-Up In this blog post we discussed the basics of the Tungsten Connector, the Keep-alive feature and how to tune the values that control it. To learn about Continuent solutions in general, check out https://www.continuent.com/solutions The Library Please read the docs! For more information about Tungsten Connector Keep-alives, please visit http://docs.continuent.com/tungsten-clustering-6.0/connector-states-keepalive.html Tungsten Clustering is the most flexible, performant global database layer available today – use it underlying your SaaS offering as a strong base upon which to grow your worldwide business! For more information, please visit https://www.continuent.com/solutions Want to learn more or run a POC? Contact us.

  • Meet Codership, the makers of Galera Cluster, at DataOps Barcelona 20-21 June
    Codership, the makers of Galera Cluster are proud to be sponsors at the second annual DataOps.Barcelona happening June 20-21 2019 at the World Trade Centre in Barcelona, Spain. For an opening keynote, in the Auditorium from 9.30-10.30am see Colin Charles speak about What’s New in Galera Cluster 4. There are plenty of new features and it debuts in MariaDB Server 10.4, so expect to hear a lot about what is available. On day two, Colin will also speak about Running MySQL and MariaDB Server securely in 2019. Our booth will have Vlad Alexandru manning it all the time, and we would love to talk to you about Galera Cluster, roadmaps, plans, as well as our support, training and consulting for Galera Cluster for MySQL as well as Percona XtraDB Cluster (PXC). Galera Cluster will also be running a raffle, so drop by Galera Cluster booth, chat with our friendly folk, and be in the running to win a pair of Bose noise cancelling headphones!

  • SQL JOINS Tutorial For Beginners | SQL JOINS Example
    SQL JOINS Tutorial For Beginners | SQL JOINS Example is today’s topic. SQL is the special-purpose programming language designed for managing information in the relational database management system (RDBMS). The word relational is key; it specifies that the DBMS is organized in such a way that there are clear relations defined between the different sets of data. SQL joins are used to combine the records from two or more tables in a database. SQL JOIN clause is used to combine the rows from two or more tables, based on a related column between them. SQL JOINS Tutorial For Beginners Different types of joins are: INNER JOIN LEFT JOIN RIGHT JOIN FULL JOIN CROSS JOIN SELF JOIN #INNER JOINS in SQL SQL Inner Join is used to select all the rows from tables for the match between the columns in tables. SQL INNER JOIN is based on the concept of EQUI JOINS. EQUI JOINS are those who use the comparison operator (=) for combining records from two or more tables. When the condition is satisfied, column values for each matched pair of rows of two tables are combined into a result row.   The shaded part above Shows a common records between both the tables. SYNTAX Select columns from Table_1 INNER JOIN Table_2 on Table_1.column = Table_2.column; So, in the above statements columns represent the column names of the tables. Table_1 and Table_2 are the names of tables. And the condition i.e Table_1.column = Table_2.column is used to compare the columns which are common in both the tables. Table_1: Employee Emp_id Emp_name City State Salary 101 Rohit Patna Bihar 30000 201 Shivam Jalandhar Punjab 20000 301 Karan Allahabad Uttar Pradesh 40000 401 Suraj Kolkata West Bengal 60000 501 Akash Vizag Andhra Pradesh 70000   Table_2: Department Dept_no Emp_id 123 301 214 401 125 505   See the following query. Select Department.Dept_no, Employee.Emp_name, Employee.City, Employee.Salary from Department INNER JOIN Employee on Department.Emp_id = Employee.Emp_id;   #Left Join in SQL Left outer join returns all rows in the table which is on the left side matched with the rows of a table in right side. This gives the conclusion that the SQL left Join always contains the rows in the left table.   The above Venn diagram shows that the left table rows will always be displayed whether the conditions match or not. SYNTAX Select column_1, column_2… from table_1 LEFT JOIN table_2 ON CONDITION; See the following tables. CUSTOMER: ID NAME AGE CITY 1 Rohit 20 Patna 2 Shivam 18 Jalandhar 3 Pranav 19 Dharamshala   ORDERS: O_ID Cust_ID City AMOUNT 201 1 Patna 3000 202 2 Jalandhar 4000 203 4 Kolkata 1000   Let’s clear this with an example. Select Orders.O_ID, Customer.id, customer.name, Orders.amount From CUSTOMER LEFT JOIN Orders ON Customer.ID = Orders.Cust_ID; See the following output.   As you can see, all the contents of a left table are displayed, whether it is matched with a right table or not. Right table contents which are matched with the left table is displayed as well and which are not matched is displayed with NULL values. #RIGHT JOIN in SQL The SQL right join returns all the values from the rows of a right table. It also includes a matched values from a left table, but if there is no matching in both the tables, it returns the NULL values.   The above Venn diagram illustrates that all the rows of the right table will be displayed whether the condition matches or not. SYNTAX Select column_1, column_2… from table_1 RIGHT JOIN table_2 ON CONDITION; CUSTOMER: ID NAME AGE CITY 1 Rohit 20 Patna 2 Shivam 18 Jalandhar 3 Pranav 19 Dharamshala   ORDERS: O_ID Cust_ID City AMOUNT 201 1 Patna 3000 202 2 Jalandhar 4000 203 4 Kolkata 1000   Query Select Orders.O_ID,Customer.id,customer.name,Orders.amount From CUSTOMER RIGHT JOIN Orders ON Customer.ID = Orders.Cust_ID; OUTPUT   EXPLANATION As you can see, all the contents of the right table are displayed, whether it is matched with a left table or not. Left table contents which are matched with a right table is displayed as well and which are not matched is displayed with the NULL values. #FULL JOIN in SQL SQL full join returns all the rows in the left table, right table and matching rows in both the tables or you can say it is a combination of left and right join. It is also known as a full outer join.   The above Venn diagram illustrates that all the rows of both the table will be displayed whether the conditions match or not. SYNTAX Select * from table1 FULL OUTER JOIN table2 ON CONDITION; Let’s consider a table. CUSTOMER: ID NAME AGE CITY 1 Rohit 20 Patna 2 Shivam 18 Jalandhar 3 Pranav 19 Dharamshala   ORDERS: O_ID Cust_ID City AMOUNT 201 1 Patna 3000 202 2 Jalandhar 4000 203 4 Kolkata 1000   Let’s clear this with an example. Select * from Customer FULL OUTER JOIN Orders ON Customer.ID = Orders.cust_id; OUTPUT The above Statement will not work in MySQL, Because the SQL full outer join returns the result set that is combined results of both SQL left join and SQL right join. So to generate the result, we have to use the UNION operator. Statement Select * from Customer LEFT JOIN Orders ON Customer.id = Orders.Cust_ID UNION Select * from Customer RIGHT JOIN Orders ON Customer.id = Orders.Cust_id; See the output.   #CROSS JOIN in SQL SQL Cross Join is used to join the table having no condition in which all the records of the first table comes with all the records of the second table. This type of Join is also called a Cartesian product. Unlike the INNER JOIN or LEFT JOIN, the cross join does not establish a relationship between the joined tables. NOTE: If Where Condition is not used with CROSS JOIN, then it will behave like a cartesian product.   Here arrows are pointing to the rows of a table. SYNTAX SELECT COLUMNS_NAME FROM TABLE_1 CROSS JOIN TABLE_2; Suppose there are two tables.   QUERY Select * from STUDENT CROSS JOIN COURSE; OUTPUT   All the combinations of rows are listed. #SELF JOIN in SQL In the self join table is joined itself, i.e. each row is joined with itself and all other rows depending on the conditions.   Syntax SELECT a.column1, b.column2 from table_name a, table_name b where condition; Table: (Employee) Emp_id Emp_name City State Salary 101 Rohit Patna Bihar 30000 201 Shivam Jalandhar Punjab 20000 301 Karan Allahabad Uttar Pradesh 40000 401 Suraj Kolkata West Bengal 60000 501 Akash Vizag Andhra Pradesh 70000   QUERY Select a.emp_name,b.salary from employee a,employee b where a.salary < b.salary; OUTPUT   Finally, SQL JOINS Tutorial For Beginners | SQL JOINS Example is over. The post SQL JOINS Tutorial For Beginners | SQL JOINS Example appeared first on AppDividend.

  • Our recap of the Percona Live Conference in Austin
    We were pleased to sponsor the Percona Live Conference in Austin this year: many thanks to the Percona Team for organising a smooth conference yet again! This is the recap of our week in Texas! At The Conference This year’s conference was the first one not taking place in Santa Clara, CA, but rather in Austin, TX. This turned out to be a nice choice by Percona, as it meant that open source database users who may not have travelled to California in the past, were attracted to the new location; and Austin being the new hot spot for (tech) companies at the moment, a lot of “locals” seemed to have made the choice to attend the conference. It was great to meet many new faces as a result. As Diamond Sponsors of the conference we were of course present with a booth in the exhibition hall, as well as with three talks. And while the hotel looked slightly dystopian at night, it was in fact a nice and pleasant location to spend the week. Our Announcements We had a couple of announcements that coincided with the Percona Live conference week and it was great to be able to present and discuss these with the attendees ‘hot off the press’ on site. New Tungsten Replicator (AMI) First off, we announced the immediate availability of the new Tungsten Replicator (AMI). Tungsten Replicator (AMI) is a replication engine that provides high-performance and improved replication functionality over the native MySQL replication solution and provides an ability to apply real-time MySQL data feed into a range of analytics and big data databases. Users can now replicate directly from AWS Aurora, AWS RDS MySQL, MySQL, MariaDB & Percona Server into popular analytic repositories such as MySQL (all variations), PostgreSQL, AWS RedShift, Kafka and Vertica​ from as little as $0.50/hour. Find out more in our announcement blog. New Partnership for MySQL & MariaDB Availability Solutions with Datavail We were also happy to announce a new partnership with Datavail to provide solutions for continuous & highly available MySQL, Percona Server & MariaDB database operations based on Tungsten Clustering & Datavail Database Services. Datavail is a renowned, tech-enabled data management, applications, business intelligence, and software solutions provider with a team of 700+ DBAs that look after customers’ database environments. Find out more on the partnership announcement. Our (And One Of Our Customer’s) Talks The Color of Open Source Money – Are some open source business models more acceptable than others? By Eero Teerikorpi, Founder & CEO The color of open source money is much discussed these days, and with this talk, Eero examines some of the aspects of it a little closer, and offers his perspective on the different shades of open source money and acceptable balance on commercial efforts to justify the investments needed for the sustainability of a given project. Watch the recording of the talk below: https://continuent-videos.s3.amazonaws.com/Eero_Keynote_Percona_Live_Austin_2019.mp4 Moving Data in real-time into Amazon Redshift By Matthew Lang, Director Professional Services Amazon Redshift has been providing scalable, quick-to-access analytics platforms for many years, but the question remains: how do you get the data from your existing datastore into Redshift for processing? Find out by reading the slides to the talk below: Download Redshift Slides Building geo clusters with AWS Aurora – can we make it better? By Matthew Lang, Director Professional Services In this talk, our colleague Matt explores how to build and deploy a geo-scale MySQL / MariaDB / Percona cloud back-end by covering these three key topics: Overview of Amazon Aurora cross region Common challenges when using Amazon Aurora How can multi-region MySQL / MariaDB / Percona deployments be improved? Find out more about these topics by going through the slides below: Download Aurora Slides Globalizing Player Accounts with MySQL at Riot Games Tyler Turk, Senior Infrastructure Engineer In this talk, Tyler briefly overviews the evolution of the Riot Games player accounts services from legacy isolated datacenter deployments to a globally replicated database cluster fronted by their account services and outlines some of the growing pains and experiences that got them to where they are today. Access Riot Games Slides Here What Also Happened At the Conference For this year’s Community Dinner, PlanetScale and Percona invited us to a unique view of Austin by taking attendees to the water. As one of the best ways to see the famous Austin bat colonies, they’d arranged a dusk-time river cruise on the Lone Star River Boat, which was a very enjoyable way to experience the city from a different perspective (though the bats mostly decided to stay put that evening). And speaking of the famous Austin bats: we finished the conference by patiently waiting for the bats to appear from inside and under the bridge near the conference hotel, where they live. It took a couple of hours on the Thursday evening, but it was well worth the wait. Thanks to everyone who attended, spoke at, organised and sponsored the conference: we’re looking forward to hopefully seeing you all again at the next one