Notes/errata/updates for Chapter 6:
See the official book errata at http://tahaghoghi.com/LearningMySQL/errata.php – Chapter 6 includes pages 179 – 222.

Other notes:
At the end of the “Creating Tables” section (p.183-4), it says “We like using the underscore character to separate words, but that’s just a matter of style and taste; you could use underscores or dashes, or omit the word-separating formatting altogether.” While this is true, beware of using a dash, because MySQL will try to interpret “two-words”, thinking – is a minus sign. I recommend avoiding dashes for this reason (even though the book does this on page 215).

At the end of the “Collation and Character Sets” section (p.186), it says “When you’re creating a database, you can set the default character set and sort order for the database and its …

Introduction Dealing with time zones is always challenging. As a rule of thumb, it’s much easier if all date/time values are stored in the UTC format, and, if necessary, dealing with time zone conversations in the UI only. This article is going to demonstrate how you can accomplish this task with JDBC and the awesome … Continue reading How to store date, time, and timestamps in UTC time zone with JDBC and Hibernate →

It's usually a good practice to have two columns called date_created and date_updated in every table. One can always use it in application and it helps in debugging too as to when a record was created and last updated in various circumstances and contexts.




This responsibility can be given to MySQL to automatically assign current time values to these columns.

In MySQL 5.6 onwards, this can be done by setting the data type of the columns to be either date time or timestamp and creating date_created column with NOT NULL DEFAULT CURRENT_TIMESTAMP  schema and date_updated column with NOT NULL DEFAULT …

Twice last week, a developer wanted to convert the existing datetime values in a database to UTC. The datetime values were the default for the server, which was the US/Pacific time zone, which is subject to Daylight Saving Time changes. Both developers for both applications wanted to convert all the times to UTC, so there would not be any changes due to Daylight Saving Time, and asked me for an easy query to know which times should be changed by adding 7 hours and which times should have 8 hours added to them.

The good news is that MySQL has some built-in functionality to make this easier. You may know about the CONVERT_TZ() function in MySQL, and that you can use it in a query to convert times like this:

mysql> SELECT CONVERT_TZ(NOW(),'-8:00','-0:00');
+-----------------------------------+
| …

Sheeri K. Cabral at the Mozilla Foundation wrote about an issue with the June 30th 2012 leap second affecting at least MySQL, Java and Minecraft servers. It now appears that the underlying cause is a Linux kernel bug, as noted by John Stultz (IBM) on the Linux Kernel mailing list, and the team Sheeri is part of deserves due credit for doing awesome pattern recognition and being the first to bring it to public attention, enabling people to quickly correlate their own experience with that of others and finding a practical solution as well as helping figure out the cause.

Sheeri’s original post MySQL and the Leap Second, High CPU and the Fix describes how MySQL servers would suddenly exhibit high CPU usage during a period of low load. …

In facebook stream you’ll see the time period at the bottom of the stream. For example: 4 minutes ago, 2 days ago, 3 weeks ago…. In our recent project we have to show similar time fashion for our application’s activity stream. So I write a function to retrieve the time duration. In our mysql database, [...]

…

When I celebrated my birthday, I got the idea that there must theoretically be a date on which two peoples ages are in a certain ratio. The basic idea were born when I thought that there must be a date on which my mother is exactly twice as old as me. Actually, this is the point of time when I'm as old as my mother was, when I were born. That means

Read the rest »

This is the 182nd edition of Log Buffer, the weekly review of database blogs. Make sure to read the whole edition so you do not miss where to submit your SQL limerick!

This week started out with me posting about International Women’s Day, and has me personally attending Confoo (Montreal) which is an excellent conference I hope to return to next year. I learned a lot from confoo, especially the blending nosql and sql session I attended.

This week was also the Hotsos Symposium. …

In this article I’m showing how to retrieve result based on today, week and month using mysql query. To learn the techniques just visit http://thinkdiff.net/mysql/getting-rank-today-this-week-and-this-month/

In my previous article I’ve shown how to get rank using mysql query. Now I’m showing how to retrieve result based on today, week and month using mysql query. Actually I also implemented this in my quiz project so I’m sharing this with you.

For the table structure please look my previous article http://thinkdiff.net/mysql/how-to-get-rank-using-mysql-query/

Task 1: I’ve to retrieve those users rank who played the game today.
Solution: Look at the query

SELECT uid, participated, correct, wrong from quiz_user
    WHERE DAYOFMONTH(CURDATE())=extract(day from updated)
    ORDER BY correct DESC, participated ASC
    limit 30

So the above query returns the result of those users who played today. Here

CURDATE() …