Category: MySQL

MySQL DATE_FORMAT: Grouping Date & Time value fields into single days made easy

09/24/2015 Aaron Belchamber No Comments

To conform a datetime field to just a date in your results and make it easy to group TIMESTAMP fields in the format “2015-12-25 12:23:23” so you can group any datetime value into actual days for daily reports, use “DATE_FORMAT” below:

DATE_FORMAT(datetime_field, '%Y-%m-%d')

In other words, if you have values from a SELECT statement that are date AND time stamps and you want to group all those values into the DAY, DATE_FORMAT converts the value for you.

So, with these values in the field “created_at”:

2015-03-01 11:11:11
2015-03-01 16:13:132015-03-01 19:01:01
2015-03-03 11:19:23

The following MySQL SELECT statement

SELECT DATE_FORMAT(created_at,'%Y-%m-%d') as created_at_days FROM your_table GROUP BY created_at_days

Will result in the following results:
2015-03-01
2015-03-03


Planning to design or redesign an organization’s database? Get back to basics and refer to MySQL’s reserved keyword list

06/16/2015 Aaron Belchamber No Comments

Oracle’s MySQL is a staple in the open source web environment world.  Being familiar with the reserved words the database uses is just one way to make sure you don’t have to refactor code or special-escape field names in queries later with the menacing back tick: `

Here is the link to MySQL’s reserved keyword’s list.  The MySQL keywords table is very useful to use in order to avoid collisions up front when designing new databases and troubleshooting possible problems of legacy systems.

I design, and redesign a lot of databases for different organizations and companies.  Planning and designing a database will require naming tables and fields logically so a successor can navigate the data and coders can troubleshoot problems without reinventing the wheel — it makes the system’s design an efficient part of the overall system of the organization.  This leads to less time wasted, more stable systems, and more up-time later.  That’s why when it comes to web development and software programming I’m a Business Systems Architect first– then many other job titles second.

– Aaron Belchamber


MySQL: Using sub-selects to filter results of one table that is not contained in another table

03/16/2015 Aaron Belchamber No Comments

In MySQL, if you have two tables with a shared index and you would like to show the results of one table where the index is not within another table, you can use “NOT IN”.

SELECT * FROM table2 WHERE some_field not in 
    (SELECT some_field FROM table1)

Try Googling “MySQL NOT IN” and you get everything but this command, it’s almost as if they need to have an alias for this simple command since these two words are in EVERYTHING and on so many web pages. Perhaps “NOTCONTAINEDIN” would be better, all in one word? Just a suggestion.

In the sub-select, the query inside parenthesis, you can take this a step further and apply conditions to the filter so you can exclude results in “table2” more narrowly than just being in “table1”, perhaps if “table1” had a status of “EXPIRED” in a field, then you could add that criteria to be more selective.


MySQL: Dealing with time and date/time database values set inside fields that are defined as text, strings, or mediumtext

08/29/2014 Aaron Belchamber No Comments

It’s happened to all of us at some point. You take over a project and some knucklehead who designed the database was too lazy to optimize it. This usually means you will end up with fields named “DateBought” and though it’s supposed to always hold a date and/or datetime value, it’s defined as some VARCHAR 255 character length field. To make matters worse, sometimes the dates are normalized so some dates are entered “2014-05-01” and other times perhaps “05/01/2014”. What do you do? First, fire the person who designed this database, or redesignate them to the graphic design position where maybe they’re better at designing layouts that won’t ruin your business systems and wreak havoc on your reporting.

Here’s a list of handy MySQL time conversions and functions I use a lot: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

Until then, if you need to rescue… I mean extract the data, you have to convert the text values of the funky fields into time values that are quantifiable. “STR_TO_DATE” is usually the answer to this kind of problem:

STR_TO_DATE(transdate, '%Y-%m-%d' ) >= '2014-05-01'

Changing MySQL “INSERT INTO” into “INSERT IGNORE INTO” can solve a lot of headaches when batch importing a lot of records

08/28/2014 Aaron Belchamber No Comments

Inserting a batch of records into MySQL will stop on the first instance the record already exists with a matching PRIMARY KEY. If you’re at risk of this happening, open the .sql file and use your text editor to search for “INSERT INTO” and replace it with “INSERT IGNORE INTO” and this will skip any existing records with the same primary key value without stopping the batch process.

INSERT IGNORE INTO

Simple, yes? But so many people seem to search for this solution. I was aware of it but seldom use it so I had to look it up, that’s why I put it in the MySQL section here on http://tools.belchamber.us so it’s easy to find and be reminded of this solution.