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: time (reset)

Recovering temporal types in MySQL 5.6: TIME, TIMESTAMP and DATETIME
+2 Vote Up -0Vote Down

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.

As of

  [Read more...]
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...]
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...]
Detecting invalid and zero temporal values
+1 Vote Up -0Vote Down

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 MIN() and 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
  •   [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...]
    Follow-up To Loading CSS And JS Conditionally
    +0 Vote Up -0Vote Down

    First of all, I'd like to thank everyone who read and gave their 2 cents about the [WordPress Plugin Development] How To Include CSS and JavaScript Conditionally And Only When Needed By The Posts post. The article was well received and will hopefully spark some optimizations around loading styles and scripts.

    Here are some discussions and mentions around the web:

      [Read more...]
    PHP Hacking
    +0 Vote Up -0Vote Down

    I don't feel like it to change my patches with every new release of PHP. In order to give something back to the community and to support my laziness, I publish a few patches for the PHP language. Some are really useful, some are quite okay and others are only to help in a more particular case. But read over the new featureset to make you an own opinion.

    Read the rest »

    Creating a MySQL plugin to produce an integer timestamp
    Employee +2 Vote Up -0Vote Down

    This article shows how to create a MySQL-plugin that can be used to create a function which can in turn be used in stored procedures. The function will produce an integer value representing the time (to the nearest usec).

    I’m working on an article for conflict detection/resolution when using MySQL Cluster asynchronous replication which requires an integer column to store a timestamp for comparison purposes. In fact, it doesn’t actually need the timestamp to represent an absolute or even a relative point in time – all it cares about is that the if the function is called twice on 2 different hosts that the 2nd call will always result in a larger number than the 1st. Obviously, in a production environment the times on the 2 hosts would need to be kept in sync.

    The c code (inttime.c)

    #include <mysql.h>
      [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...]
    Does anybody really know what time it is?
    +0 Vote Up -0Vote Down

    This is a post about SYSDATE() and NOW() and CURRENT_TIMESTAMP() functions in MySQL.

    Firstly, note is that of these three, only CURRENT_TIMESTAMP() is part of the SQL Standard. NOW() happens to be an alias for CURRENT_TIMESTAMP() in MySQL.

    Secondly, note that replication does not work well with non-deterministic functions. And “hey, what time is it?” is non-deterministic. Ask it twice, with a second apart between asking, and both times you get different results (with at least second precision).

    You can start to see the problem here….but there’s more…. (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.


    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.