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 →
6 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.
In this post, we’ll discuss how MySQL 5.7 handles the old temporal types during an upgrade.
MySQL changed the temporal types in MySQL 5.6.4, and it introduced a new feature: microseconds resolution in the TIME, TIMESTAMP and DATETIME types. Now these parameters can be set down to microsecond granularity. Obviously, this means format changes, but why is this important?
Are they converted automatically to the new format?
If we had tables in MySQL 5.5 that used TIME, TIMESTAMP or DATETIME are these fields are going to be converted to the new format when upgrading to 5.6? The answer is “NO.” Even if we …[Read more]
This week we are talking about size, which is a subject that should matter to any system administrator in charge of the backup system of any project, and in particular database backups.
I sometimes get questions about what should be the best compression tool to apply during a particular backup system: gzip? bzip2? any other?
The testing environment
In order to test several formats and tools, I created a .csv file (comma-separated values) that was 3,700,635,579 bytes in size by transforming a recent dump of all the OpenStreetMap nodes of the European portion of Spain. It had a total of 46,741,126 rows and looked like this:
171773 38.6048402 -0.0489871 4 2012-08-25 00:37:46 12850816 472193 rubensd 171774 38.6061981 -0.0496867 2 2008-01-19 10:23:21 666916 9250 …[Read more]
MySQL 5.6 introduces a new feature – microseconds resolution in some temporal types. As of 5.6.4 TIME, TIMESTAMP and DATETIME can have a fractional part. To create a field with subseconds you can specify precision in brackets: TIME(3), DATETIME(6) etc.
Obviously, the new feature requires the format change. All three types may now have a tail with a fractional part. The tail may be one, two or three bytes long and can store up to six digits after the point.
The non-fractional part has changed too. Thus, DATETIME uses only five bytes comparing to eight in previous versions.[Read more]
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');
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]
I’ve been thinking a lot about invalid and zero temporal values
and how to detect them with MySQL date and time functions because
mk-table-checksum has to handle “everything” correctly and
efficiently. The requirements are complex because we have to take
into account what MySQL allows to be stored verses what it allows
to be used in certain operations and functions, how it sorts a
mix of real and invalid temporal values for
MAX(), how to detect a temporal value as
equivalent to zero, and how different MySQL versions might affect
any of the aforementioned.
At base, the four guiding requirements are:
- Detect and discard invalid time, date, and datetime values
- Detect zero-equivalent temporal values
- Do #1 and #2 using only MySQL functions
- Work in MySQL 4.0 …
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]
6 Older Entries »