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 →
4 Older Entries »
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 '0000-00-00 00:00:00' as schema with attribute ON UPDATE CURRENT_TIMESTAMP.
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. …[Read more]
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, [...][Read more]
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
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. …[Read more]
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
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() …[Read more]
I see my collague Anders wrote some thoughts on date handling in MySQL. I recently had to explain this really down to fine details for a customer, so Anders, everyone else, here's my conclusions...
All the fine details of allowed ways to enter dates are explained
With dates, just as in many other cases, MySQL is very flexible in how you can input data. But one thing is that, between the multitudes of formats that are allowed, MySQL does *not* by default allow you to enter a non-existant date, e.g. "31st of February 2009". I know some other DB products allow that - they then store it as a correct date automatically (3rd of March 2009). This is mentioned at the end of …[Read more]
4 Older Entries »