Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 12

Displaying posts with tag: date (reset)

Converting Timezone-Specific Times in MySQL
+0 Vote Up -0Vote Down

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

  [Read more...]
The 2012 Leap Second on Linux
+2 Vote Up -0Vote Down

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

  [Read more...]
Retrieve time period like facebook fashion
+1 Vote Up -3Vote Down
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...]
Calculate date ratios
+0 Vote Up -0Vote Down

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 »

Log Buffer #182, a Carnival of the Vanities for DBAs
+3 Vote Up -0Vote Down

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. Doug’s

  [Read more...]
MySQL query that get ranks today, this week and this month
+1 Vote Up -0Vote Down

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/


Getting rank today, this week and this month
+0 Vote Up -0Vote Down

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,

  [Read more...]
Date handling in MySQL
Employee_Team +0 Vote Up -0Vote Down

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 here:
http://dev.mysql.com/doc/refman/5.1/en/datetime.html

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


  [Read more...]
Date handling in MySQL
Employee_Team +0 Vote Up -0Vote Down

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 here:
http://dev.mysql.com/doc/refman/5.1/en/datetime.html

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


  [Read more...]
Date handling in MySQL
Employee_Team +0 Vote Up -0Vote Down

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 here:
http://dev.mysql.com/doc/refman/5.1/en/datetime.html

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


  [Read more...]
Pear::Date Returned Timezone is Wrong
+0 Vote Up -0Vote Down

Background Knowledge


I’m trying to determine the difference in minutes between two timestamps. I’m using Pear::Date to do this. The issue comes into play when I noticed that the wrong timezone was being used by Pear::Date, UTC. If I do not use Pear::Date the timezone is set correctly.

I have tried using date_default_timezone_set() and it does set the timezone back, however I feel this shouldn’t be necessary as the default timezone should be used. I have been using date_default_timezone_get() to determine what timezone is being used.

It’s my understanding that Pear::Date uses UTC when it is unable to determine the default timezone. As far as I know I

  [Read more...]
How To - Convert MySQL Timestamp/Datetime to Unix Timestamp
+0 Vote Up -0Vote Down

Background Knowledge


Since MySQL v4.1 timestamp and datetime data types are formatted “YYYY-MM-DD HH:MM:SS”. Prior to MySQL v4.1 the timestamp was formatted as YYYYMMDDHHMMSS” and datetime formatted as “YYYY-MM-DD HH:MM:SS”. Refer to MySQL Reference Manual for further details.

The Unix timestamp differs from MySQL. Unix’s timestamp is a integer value of seconds since January 1, 1970. For further explanation of Unix timestamps refer to Wikiepedia or UnixTimestamp.com.

Solutions


In MySQL you can use

  [Read more...]
Showing entries 1 to 12

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.