There are several ways to add and to change data in MariaDB. There are a few SQL statements that you can use, each with a few options. Additionally, there are twists that you can do by mixing SQL statements together with various clauses. In this article, we will explore the ways in which data can be added and changed in MariaDB.

Adding Data

To add data to a table in MariaDB, you will need to use the INSERT statement. Its basic, minimal syntax is the command INSERT followed by the table name and then the keyword VALUES with a comma separated list of values contained in parentheses:

In this example, text is added to a table called table1, which contains only three columns—the same number of values that we're inserting. The number of columns must match. If you don't want to insert data into all of the columns of a table, though, you could name the columns desired:

Notice that the keyword INTO was added here. This is optional and has no effect on MariaDB. It's only a matter of grammatical preference. In this example we not only name the columns, but we list them in a different order. This is acceptable to MariaDB. Just be sure to list the values in the same order. If you're going to insert data into a table and want to specify all of the values except one (say the key column since it's an auto-incremented one), then you could just give a value of DEFAULT to keep from having to list the columns. Incidentally, you can give the column names even if you're naming all of them. It's just unnecessary unless you're going to reorder them as we did in this last example.

When you have many rows of data to insert into the same table, it can be more efficient to insert all of the rows in one SQL statement. Multiple row insertions can be done like so:

Notice that the keyword VALUES is used only once and each row is contained in its own set of parentheses and each set is separated by commas. We've added an intentional mistake to this example: We are attempting to insert three rows of data into table2 for which the first column happens to be a UNIQUE key field. The third row entered here has the same identification number for the key column as the second row. This would normally result in an error and none of the three rows would be inserted. However, since the statement has an IGNORE flag, duplicates are ignored and not inserted, but the other rows will still be inserted. So, the first and second rows above are inserted and the third one won't.

Priority

An INSERT statement takes priority over read statements (i.e., statements). An INSERT will lock the table and force other clients to wait until it's finished. On a busy MariaDB server that has many simultaneous requests for data, this could cause users to experience delays when you run a script that performs a series of INSERT statements. If you don't want user requests to be put on hold and you can wait to insert the data, you could use the LOW_PRIORITY flag:

The LOW_PRIORITY flag will put the INSERT statement in queue, waiting for all current and pending requests to be completed before it's performed. If new requests are made while a low priority statement is waiting, then they are put ahead of it in the queue. MariaDB does not begin to execute a low priority statement until there are no other requests waiting. Once the transaction begins, though, the table is locked and any other requests for data from the table that come in after it starts must wait until it's completed. Because it locks the table, low priority statements will prevent simultaneous insertions from other clients even if you're dealing with a MyISAM table. Incidentally, notice that the LOW_PRIORITY flag comes before the INTO.

One potential inconvenience with an INSERT LOW_PRIORITY statement is that the client are tied up waiting for the statement to be completed successfully. So if you're inserting data into a busy server with a low priority setting using the mariadb client, your client could be locked up for minutes, maybe hours depending on how busy your server is at the time. As an alternative either to making other clients with read requests wait or to having your client wait, you can use the DELAYED flag instead of the LOW_PRIORITY flag:

MariaDB will take the request as a low priority one and put it on its list of tasks to perform when it has a break. However, it will immediately release the client so that the client can go on to enter other SQL statements or even exit. Another advantage of this method is that multiple INSERT DELAYED requests are batched together for block insertion when there is a gap, making the process potentially faster than INSERT LOW_PRIORITY. The flaw in this choice, however, is that the client is never told if a delayed insertion is successfully made or not. The client is informed of error messages when the statement is entered—the statement has to be valid before it are queued—but it's not told of problems that occur after it's accepted. This brings up another flaw: delayed insertions are stored in the server's memory. So if the MariaDB daemon (mariadbd) dies or is manually killed, then the transactions are lost and the client is not notified of the failure. So is not always a good alternative.

Contingent Additions

As an added twist to INSERT, you can combine it with a SELECT statement. Suppose that you have a table called employees which contains employee information for your company. Suppose further that you have a column to indicate whether an employee is on the company's softball team. However, you one day decide to create a separate database and table for the softball team's data that someone else will administer. To get the database ready for the new administrator, you have to copy some data for team members to the new table. Here's one way you can accomplish this task:

In this SQL statement the columns in which data is to be inserted into are listed, then the complete SELECT statement follows with the appropriate WHERE clause to determine if an employee is on the softball team. Since we're executing this statement from the new database and since the table employees is in a separate database called company, we have to specify it as you see here. By the way, statements cannot be performed on the same table.

Replacement Data

When you're adding massive amounts of data to a table that has a key field, as mentioned earlier, you can use the IGNORE flag to prevent duplicates from being inserted, but still allow unique rows to be entered. However, there may be times when you actually want to replace the rows with the same key fields with the new ones. In such a situation, instead of using INSERT you can use a statement:

Notice that the syntax is the same as an INSERT statement. The flags all have the same effect, as well. Also, multiple rows may be inserted, but there's no need for the IGNORE flag since duplicates won't happen—the originals are just overwritten. Actually, when a row is replaced, it's first deleted completely and the new row is then inserted. Any columns without values in the new row are given the default values for the columns. None of the values of the old row are kept. Incidentally, REPLACE will also allow you to combine it with a SELECT statement as we saw with the INSERT statement earlier.

Updating Data

If you want to change the data contained in existing records, but only for certain columns, then you would need to use an statement. The syntax for UPDATE is a little bit different from the syntax shown before for and statements:

In the SQL statement here, we are changing the value of the two columns named individually using the SET clause. Incidentally, the SET clause optionally can be used in INSERT and REPLACE statements, but it eliminates the multiple row option. In the statement above, we're also using a WHERE clause to determine which records are changed: only rows with an id that has a value less than 100 are updated. Notice that the LOW_PRIORITY flag can be used with this statement, too. The IGNORE flag can be used, as well.

A useful feature of the UPDATE statement is that it allows the use of the current value of a column to update the same column. For instance, suppose you want to add one day to the value of a date column where the date is a Sunday. You could do the following:

For rows where the day of the week is Sunday, the function will take the value of col_date before it's updated and add one day to it. MariaDB will then take this sum and set col_date to it.

There are a couple more twists that you can now do with the UPDATE statement: if you want to update the rows in a specific order, you can add an clause. You can also limit the number of rows that are updated with a clause. Below is an example of both of these clauses:

The ordering can be descending as indicated here by the DESC flag, or ascending with either the ASC flag or by just leaving it out, as ascending is the default. The LIMIT clause, of course, limits the number of rows affected to ten here.

If you want to refer to multiple tables in one UPDATE statement, you can do so like this:

Here we see a join between the two tables named. In table3, the value of col1 is set to the value of the same column in table4 where the values of id from each match. We're not updating both tables here; we're just accessing both. We must specify the table name for each column to prevent an ambiguity error. Incidentally, ORDER BY and LIMIT clauses aren't allowed with multiple table updates.

There's another combination that you can do with the INSERT statement that we didn't mention earlier. It involves the UPDATE statement. When inserting multiple rows of data, if you want to note which rows had potentially duplicate entries and which ones are new, you could add a column called status and change it's value accordingly with a statement like this one:

Because of the IGNORE flag, errors will not be generated, duplicates won't be inserted or replaced, but the rest are added. Because of the , the column status of the original row are set to old when there are duplicate entry attempts. The rest are inserted and their status set to new.

Conclusion

As you can see from some of these SQL statements, MariaDB offers you quite a few ways to add and to change data. In addition to these methods, there are also some bulk methods of adding and changing data in a table. You could use the statement and the command-line utility. These methods are covered in another article on .

_{This page is licensed: CC BY-SA / Gnu FDL}

When a MariaDB developer first creates a MariaDB database for a client, often times the client has already accumulated data in other, simpler applications. Being able to convert data easily to MariaDB is critical. In the previous two articles of this MariaDB series, we explored how to set up a database and how to query one. In this third installment, we will introduce some methods and tools for bulk importing of data into MariaDB. This isn't an overly difficult task, but the processing of large amounts of data can be intimidating for a newcomer and as a result it can be a barrier to getting started with MariaDB. Additionally, for intermediate developers, there are many nuances to consider for a clean import, which is especially important for automating regularly scheduled imports. There are also restraints to deal with that may be imposed on a developer when using a web hosting company.

Foreign Data Basics

Clients sometimes give developers raw data in formats created by simple database programs like MS Access ®. Since non-technical clients don't typically understand database concepts, new clients often give me their initial data in Excel spreadsheets. Let's first look at a simple method for importing data. The simplest way to deal with incompatible data in any format is to load it up in its original software and to export it out to a delimited text file. Most applications have the ability to export data to a text format and will allow the user to set the delimiters. We like to use the bar (i.e., |, a.k.a. pipe) to separate fields and the line-feed to separate records.

For the examples in this article, we will assume that a fictitious client's data was in Excel and that the exported text file are named prospects.txt. It contains contact information about prospective customers for the client's sales department, located on the client's intranet site. The data is to be imported into a MariaDB table called prospect_contact, in a database called sales_dept. To make the process simpler, the order and number of columns in MS Excel ® (the format of the data provided by the client) should be the same as the table into which the data is going to be imported. So if prospect_contact has columns that are not included in the spreadsheet, one would make a copy of the spreadsheet and add the missing columns and leave them blank. If there are columns in the spreadsheet that aren't in prospect_contact, one would either add them to the MariaDB table, or, if they're not to be imported, one would delete the extra columns from the spreadsheet. One should also delete any headings and footnotes from the spreadsheet. After this is completed then the data can be exported. Since this is Unix Review, we'll skip how one would export data in Excel and assume that the task was accomplished easily enough using its export wizard.

The next step is to upload the data text file to the client's web site by FTP. It should be uploaded in ASCII mode. Binary mode may send binary hard-returns for row-endings. Also, it's a good security habit to upload data files to non-public directories. Many web hosting companies provide virtual domains with a directory like /public_html, which is the document root for the Apache web server; it typically contains the site's web pages. In such a situation, / is a virtual root containing logs and other files that are inaccessible to the public. We usually create a directory called tmp in the virtual root directory to hold data files temporarily for importing into MariaDB. Once that's done, all that's required is to log into MariaDB with the client as an administrative user (if not root, then a user with FILE privileges), and run the proper SQL statement to import the data.

Loading Data Basics

The statement is the easiest way to import data from a plain text file into MariaDB. Below is what one would enter in the client to load the data in the file called prospects.txt into the table prospect_contact:

Before entering the statement above, the MariaDB session would, of course, be switched to the sales_dept database with a statement. It is possible, though, to specify the database along with the table name (e.g., sales_dept.prospect_contact). If the server is running Windows, the forward slashes are still used for the text file's path, but a drive may need to be specified at the beginning of the path: 'c:/tmp/prospects.txt'. Notice that the SQL statement above has | as the field delimiter. If the delimiter was [TAB]—which is common—then one would replace | with here. A line-feed () isn't specified as the record delimiter since it's assumed. If the rows start and end with something else, though, then they will need to be stated. For instance, suppose the rows in the text file start with a double-quote and end with a double-quote and a Windows hard-return (i.e., a return and a line-feed). The statement would need to read like this:

Notice that the starting double-quote is inside of single-quotes. If one needs to specify a single-quote as the start of a line, one could either put the one single-quote within double-quotes or one could escape the inner single-quote with a back-slash, thus telling MariaDB that the single-quote that follows is to be taken literally and is not part of the statement, per se:

Duplicate Rows

If the table prospect_contact already contains some of the records that are about to be imported from prospects.txt (that is to say, records with the same primary key), then a decision should be made as to what MariaDB is to do about the duplicates. The SQL statement, as it stands above, will cause MariaDB to try to import the duplicate records and to create duplicate rows in prospect_contact for them. If the table's properties are set not to allow duplicates, then MariaDB will kick out errors. To get MariaDB to replace the duplicate existing rows with the ones being imported in, one would add the REPLACE just before the INTO TABLE clause like this:

To import only records for prospects that are not already in prospect_contact, one would substitute REPLACE with the IGNORE flag. This instructs MariaDB to ignore records read from the text file that already exist in the table.

Live Data

For importing data into a table while it's in use, table access needs to be addressed. If access to the table by other users may not be interrupted, then a LOW_PRIORITY flag can be added to the statement. This tells MariaDB that the loading of this data is a low priority. One would only need to change the first line of the SQL statement above to set its priority to low:

If the LOW_PRIORITY flag isn't included, the table are locked temporarily during the import and other users are prevented from accessing it.

Being Difficult

I mentioned earlier that uploading of the text file should not be done in binary mode so as to avoid the difficulties associated with Windows line endings. If this is unavoidable, however, there is an easy way to import binary row-endings with MariaDB. One would just specify the appropriate hexadecimals for a carriage-return combined with a line-feed (i.e., CRLF) as the value of TERMINATED BY:

Notice that there are intentionally no quotes around the binary value. If there were, MariaDB would take the value for text and not a binary code. The semi-colon is not part of the value; it's the SQL statement terminator.

Earlier we also stated that the first row in the spreadsheet containing the column headings should be deleted before exporting to avoid the difficulty of importing the headings as a record. It's actually pretty easy to tell MariaDB to just skip the top line. One would add the following line to the very end of the statement:

The number of lines for MariaDB to ignore can, of course, be more than one.

Another difficulty arises when some Windows application wizards export data with each field surrounded by double-quotes, as well as around the start and end of records. This can be a problem when a field contains a double-quote. To deal with this, some applications use back-slash () to escape embedded double-quotes, to indicate that a particular double-quote is not a field ending but part of the field's content. However, some applications will use a different character (like a pound-sign) to escape embedded quotes. This can cause problems if MariaDB isn't prepared for the odd escape-character. MariaDB will think the escape character is actually text and the embedded quote-mark, although it's escaped, is a field ending. The unenclosed text that follows are imported into the next column and the remaining columns are one column off, leaving the last column not imported. As maddening as this can be, it's quite manageable in MariaDB by adding an ENCLOSED BY and an ESCAPED BY clause:

In the Foreign Data Basics section above, we said that the columns in the spreadsheet should be put in the same order and quantity as the receiving table. This really isn't necessary if MariaDB is cued in as to what it should expect. To illustrate, let's assume that prospect_contact has four columns in the following order: row_id, name_first, name_last, telephone. Whereas, the spreadsheet has only three columns, differently named, in this order: Last Name, First Name, Telephone. If the spreadsheet isn't adjusted, then the SQL statement will need to be changed to tell MariaDB the field order:

This SQL statement tells MariaDB the name of each table column associated with each spreadsheet column in the order that they appear in the text file. From there it will naturally insert the data into the appropriate columns in the table. As for columns that are missing like row_id, MariaDB will fill in those fields with the default value if one has been supplied in the table's properties. If not, it will leave the field as NULL. Incidentally, we slipped in the binary [TAB] (0x09) as a field delimiter.

mariadb-import

For some clients and for certain situations it may be of value to be able to import data into MariaDB without using the client. This could be necessary when constructing a shell script to import text files on an automated, regular schedule. To accomplish this, the (mysqlimport before ) utility may be used as it encompasses the statement and can easily be run from a script. So if one wants to enter the involved SQL statement at the end of the last section above, the following could be entered from the command-line (i.e., not in the client):

Although this statement is written over several lines here, it either has to be on the same line when entered or a space followed by a back-slash has to be entered at the end of each line (as seen here) to indicate that more follows. Since the above is entered at the command-line prompt, the user isn't logged into MariaDB. Therefore the first line contains the user name and password for to give to MariaDB. The password itself is optional, but the directive --password (without the equal sign) isn't. If the password value is not given in the statement, then the user are prompted for it. Notice that the order of directives doesn't matter after the initial command, except that the database and file name go last. Regarding the file name, its prefix must be the same as the table—the dot and the extension are ignored. This requires that prospects.txt be renamed to prospect_contact.txt. If the file isn't renamed, then MariaDB would create a new table called prospects and the --replace option would be pointless. After the file name, incidentally, one could list more text files, separated by a space, to process using . We've added the --verbose directive so as to be able to see what's going on. One probably would leave this out in an automated script. By the way, --low-priority and --ignore-lines are available.

Web Hosting Restraints

Some web hosting companies do not allow the use of or statements due to security vulnerabilities in these statements for them. To get around this, some extra steps are necessary to avoid having to manually enter the data one row at a time. First, one needs to have MariaDB installed on one's local workstation. For simplicity, we'll assume this is done and is running Linux on the main partition and MS Windows® on an extra partition. Recapping the on-going example of this article based on these new circumstances, one would boot up into Windows and start MS Excel®, load the client's spreadsheet into it and then run the export wizard as before—saving the file prospects.txt to the 'My Documents' directory. Then one would reboot into Linux and mount the Windows partition and copy the data text file to /tmp in Linux, locally. Next one would log into the local (not the client's) MariaDB server and import the text file using a as we've extensively outline above. From there one would exit MariaDB and export the data out of MariaDB using the utility locally, from the command-line like this:

This creates an interesting text file complete with all of the SQL commands necessary to insert the data back into MariaDB one record, one at a time. When you run , it's very educational to open up it in a text editor to see what it generates.

After creating this table dump, one would upload the resulting file (in ASCII mode) to the /tmp directory on the client's web server. From the command prompt on the client's server one would enter the following:

This line along with the line show above are simple approaches. Like the Windows application wizard, with mariadb-dump one can specify the format of the output file and several other factors. One important factor related to the scenario used in this article is the statement that are embedded in the output file. This will fail and kick out an error because of the existing table prospect_contact in the client's database. To limit the output to only statements and no statements, the line would look like this:

Notice that we've used acceptable abbreviations for the user name and the password directives. Since the password was given here, the user are prompted for it.

The utility usually works pretty well. However, one feature it's lacking at this time is a REPLACE flag as is found in the statement and with the tool. So if a record already exists in the prospect_contact, it won't be imported. Instead it will kick out an error message and stop at that record, which can be a mess if one has imported several hundred rows and have several hundred more to go. One easy fix for this is to open up prospects.sql in a text editor and do a search on the word INSERT and replace it with REPLACE. The syntax of both of these statements are the same, fortunately. So one would only need to replace the keyword for new records to be inserted and for existing records to be replaced.

Concluding Observations and Admissions

It's always amazing to me how much can be involved in the simplest of statements in MariaDB. MariaDB is deceptively powerful and feature rich. One can keep the statements pretty minimal or one can develop a fairly detailed, single statement to allow for accuracy of action. There are many other aspects of importing data into MariaDB that we did not address—in particular dealing with utilities. We also didn't talk about the Perl modules that could be used to convert data files. These can be useful in scripting imports. There are many ways in which one can handle importing data. Hopefully, this article has presented most of the basics and pertinent advanced details that may be of use to most MariaDB developers.

_{This page is licensed: CC BY-SA / Gnu FDL}

The simplest way to retrieve data from MariaDB is to use the statement. Since the statement is an essential SQL statement, it has many options available with it. It's not necessary to know or use them all—you could execute very basic statements if that satisfies your needs. However, as you use MariaDB more, you may need more powerful statements. In this article we will go through the basics of and will progress to more involved statements;we will move from the beginner level to the more intermediate and hopefully you will find some benefit from this article regardless of your skill level. For absolute beginners who are just starting with MariaDB, you may want to read the .

Creating and Populating the Tables

In order to follow the examples that follow, you can create and pre-populate the tables, as follows:

The recording of date and time in a MariaDB database is a very common requirement. For gathering temporal data, one needs to know which type of columns to use in a table. More importantly is knowing how to record chronological data and how to retrieve it in various formats. Although this is a seemingly basic topic, there are many built-in time functions that can be used for more accurate SQL statements and better formatting of data. In this article we will explore these various aspects of how to do time with MariaDB.

About Time

Since date and time are only numeric strings, they can be stored in a regular character column. However, by using temporal data type columns, you can make use of several built-in functions offered by MariaDB. Currently, there are five temporal data types available: DATE, TIME, DATETIME, TIMESTAMP, and YEAR. The DATE column type is for recording the date only and is basically in this format: yyyy-mm-dd. The TIME column type is for recording time in this format: hhh:mm:ss. To record a combination of date and time, there is the DATETIME column type: yyyy-mm-dd hh:mm:ss.

Telling Time

To record the current date and time in a MariaDB table, there are a few built-in functions that may be used. First, to record the date there are the functions and (depending on your style), which both produce the same results (e.g., 2017-08-01). Notice that requires parentheses and the other does not. With many functions a column name or other variables are placed inside of the parentheses to get a result. With functions like , there is nothing that may go inside the parenthesis. Since these two functions retrieve the current date in the format of the DATE column type, they can be used to fill in a DATE column when inserting a row:

The column session_date is a DATE column. Notice that there are no quotes around the date function. If there were it would be taken as a literal value rather than a function. Incidentally, I've skipped discussing how the table was set up. If you're not familiar with how to set up a table, you may want to read the article. To see what was just recorded by the statement above, the following may be entered (results follow):

Notice in the billable_work table that the primary key column (i.e., rec_id) is an automatically generated and incremental number column (i.e., AUTO_INCREMENT). As long as another record is not created or the user does not exit from the mariadb client or otherwise end the session, the function will retrieve the value of the rec_id for the last record entered by the user.

To record the time of an appointment for a patient in a time data type column, or are used in the same way to insert the time. The following is entered to update the row created above to mark the starting time of the appointment—another statement follows with the results:

The column session_time is a time column. To record the date and time together in the same column, or or can be used. All three functions produce the same time format: yyyy-mm-dd hh:mm:ss. Therefore, the column's data type would have to be DATETIME to use them.

How to get a Date

Although MariaDB records the date in a fairly agreeable format, you may want to present the date when it's retrieved in a different format. Or, you may want to extract part of the date, such as only the day of the month. There are many functions for reformatting and selectively retrieving date and time information. To start off with, let's select a column with a data type of DATE and look at the functions available for retrieving each component. To extract the year, there's the function. For extracting just the month, the function could be called upon. And to grab the day of the month, will work. Using the record entered above, here's what an SQL statement and its results would look like in which the session date is broken up into separate parts, but in a different order:

For those who aren't familiar with the keyword AS, it's used to label a column's output and may be referenced within an SQL statement. Splitting up the elements of a date can be useful in analyzing a particular element. If the bookkeeper of the fictitious psychiatry office needed to determine if the day of the week of each session was on a Saturday because the billing rate would be higher (time and a half), the function could be used. To spice up the examples, let's wrap the date function up in an function that tests for the day of the week and sets the billing rate accordingly.

Since we've slipped in the function, we should explain it's format. The test condition is listed first within the parentheses. In this case, the test is checking if the session date is the sixth day of the week. Then, what MariaDB should display is given if the test passes, followed by the result if it fails.

Similar to the function, there's also . The only difference is that for the first day of the week is Sunday—with the first day is Monday. Both functions represent the first day with 0 and the last with 6. Having Saturday and Sunday symbolized by 5 and 6 can be handy in constructing an IF statement that has a test component like "WEEKDAY(session_date) > 4" to determine if a date is a weekend day. This is cleaner than testing for values of 0 and 6.

There is a function for determining the day of the year: . It's not used often, but it is available if you ever need it. Occasionally, though, knowing the quarter of a year for a date can be useful for financial accounting. Rather than set up a formula in a script to determine the quarter, the function can do this easily. For instance, suppose an accountant wants a list of a doctor's sessions for each patient for the previous quarter. These three SQL statements could be entered in sequence to achieve the results that follow:

This example is the most complicated so far. But it's not too difficult to understand if we pull it apart. The first SQL statement sets up a user variable containing the previous quarter (i.e., 1, 2, 3, or 4). This variable are needed in the other two statements. The clause in the first statement checks if the quarter of the current date minus one is zero. It will equal zero when it's run during the first quarter of a year. During a first quarter, of course, the previous quarter is the fourth quarter of the previous year. So, if the equation equals zero, then the variable @LASTQTR is set to 4. Otherwise, @LASTQTR is set to the value of the current quarter minus one. The second statement is necessary to ensure that the records for the correct year are selected. So, if @LASTQTR equals four, then @YR needs to equal last year. If not, @YR is set to the current year. With the user variables set to the correct quarter and year, the statement can be entered. The function counts the number of appointments that match the WHERE clause for each patient based on the clause. The WHERE clause looks for sessions with a quarter that equals @LASTQTR

What is the Time?

The last section covered how to retrieve pieces of a date column. Now let's look at how to do the same with a time column. To extract just the hour of a time saved in MariaDB, the function could be used. For the minute and second, there's and . Let's put them all together in one straightforward statement:

Date & Time Combined

All of the examples given so far have involved separate columns for date and time. The function, however, will allow a particular component to be extracted from a combined column type (i.e., DATETIME or TIMESTAMP). The format is EXTRACT(date_type FROM date_column) where date_type is the component to retrieve and date_column is the name of the column from which to extract data. To extract the year, the date_type would be YEAR; for month, MONTH is used; and for day, there's DAY. To extract time elements, HOUR is used for hour, MINUTE for minute, and SECOND for second. Although that's all pretty simple, let's look at an example. Suppose the table billable_work has a column called appointment (a

This statement calls upon another table (patients) which holds patient information such as their names. It requires a connecting point between the tables (i.e., the patient_id from each table). If you're confused on how to form relationships between tables in a statement, you may want to go back and read the article. The SQL statement above would be used to retrieve the appointments for one doctor for one day, giving results like this:

In this example, the time elements are separated and they don't include the date. With the function, however, you can also return combined date and time elements. There is DAY_HOUR for the day and hour; there's DAY_MINUTE for the day, hour, and minute; DAY_SECOND for day, hour, minute, and second; and YEAR_MONTH for year and month. There are also some time only combinations: HOUR_MINUTE for hour and minute; HOUR_SECOND for hour, minute, and second; and MINUTE_SECOND for minute and second. However, there's not a MONTH_DAY to allow the combining of the two extracts in the WHERE clause of the last statement above. Nevertheless, we'll modify the example above and use the HOUR_MINUTE date_type to retrieve the hour and minute in one resulting column. It would only require the second and third lines to be deleted and replaced with this:

The problem with this output, though, is that the times aren't very pleasing looking. For more natural date and time displays, there are a few simple date formatting functions available and there are the and functions.

Fine Time Pieces

The simple functions that we mentioned are used for reformatting the output of days and months. To get the date of patient sessions for August, but in a more wordier format, and could be used:

In this statement the splices together the results of several date functions along with spaces and other characters. The function was eliminated from the WHERE clause and instead a simple numeric test for sessions in August was given. Although is fairly straightforward, this all can be accomplished with less typing by using the function.

The function has over thirty options for formatting the date to your liking. Plus, you can combine the options and add your own separators and other text. The syntax is DATE_FORMAT(date_column, 'options & characters'). As an example, let's reproduce the last SQL statement by using the function for formatting the date of the appointment and for scanning for appointments in July only:

This produces the exact same output as above, but with a more succinct statement. The option %W gives the name of the day of the week. The option %M provides the month's name. The option %e displays the day of the month (%d would work, but it left-pads single-digit dates with zeros). Finally, %Y is for the four character year. All other elements within the quotes (i.e., the spaces, the dash, and the comma) are literal characters for a nicer display.

With , time elements of a field also can be formatted. For instance, suppose we also wanted the hour and minute of the appointment. We would only need to change the second line of the SQL statement above (to save space, patient_name was eliminated):

The word at was added along with the formatting option %r which gives the time with AM or PM at the end.

Although it may be a little confusing at first, once you've learned some of the common formatting options, is much easier to use than . There are many more options to that we haven't mentioned. For a complete list of the options available, see the .

Clean up Time

In addition to , MariaDB has a comparable built-in function for formating only time: . The syntax is the same and uses the same options as , except only the time related formatting options apply. As an example, here's an SQL statement that a doctor might use at the beginning of each day to get a list of her appointments for the day:

The option %l provides the hours 01 through 12. The %p at the end indicates (with the AM or PM) whether the time is before or after noon. The %i option gives the minute. The colon and the space are for additional display appeal. Of course, all of this can be done exactly the same way with the function. As for the component in the WHERE clause here, the date is formatted exactly as it are with (i.e., 2017-08-30) so that they may be compared properly.

Time to End

Many developers use PHP, Perl, or some other scripting language with MariaDB. Sometimes developers will solve retrieval problems with longer scripts rather than learn precisely how to extract temporal data with MariaDB. As you can see in several of the examples here (particularly the one using the function), you can accomplish a great deal within MariaDB. When faced with a potentially complicated SQL statement, try creating it in the mariadb client first. Once you get what you need (under various conditions) and in the format desired, then copy the statement into your script. This practice can greatly help you improve your MariaDB statements and scripting code.

CC BY-SA / Gnu FDL