MySQL Order By date type issue
This must be the “gotcha” type of stuff when you think just using the “ORDER BY <date type>” clause would give you back the correct results in MySQL.
My original table structure and its data:
mysql> describe holidays; +-----------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------+--------------+------+-----+---------+----------------+ | holidayId | int(11) | NO | PRI | NULL | auto_increment | | holidayName | varchar(255) | NO | | NULL | | | holidayDate | date | NO | | NULL | | | holidayDuration | int(11) | NO | | NULL | | +-----------------+--------------+------+-----+---------+----------------+
mysql> select holidayId, holidayName, holidayDate from holidays; +-----------+-------------------+-------------+ | holidayId | holidayName | holidayDate | +-----------+-------------------+-------------+ | 1 | Memorial Day | 2014-05-26 | | 2 | Independence Day | 2014-07-04 | | 3 | Labor Day | 2014-09-01 | | 4 | Thanks Giving Day | 2014-11-27 | | 5 | Christmas Day | 2014-12-25 | | 6 | New Year's Day | 2015-01-01 | +-----------+-------------------+-------------+
Then I’d query the table with the ORDER BY clause with some DATE_FORMAT():
mysql> SELECT holidayId, holidayName, date_format(holidayDate,'%m/%d/%Y') holidayDate -> FROM holidays -> ORDER BY holidayDate; +-----------+-------------------+-------------+ | holidayId | holidayName | holidayDate | +-----------+-------------------+-------------+ | 6 | New Year's Day | 01/01/2015 | | 1 | Memorial Day | 05/26/2014 | | 2 | Independence Day | 07/04/2014 | | 3 | Labor Day | 09/01/2014 | | 4 | Thanks Giving Day | 11/27/2014 | | 5 | Christmas Day | 12/25/2014 | +-----------+-------------------+-------------+
As you can see, the New Year’s Day for 2015 is at top. It seems like the records were sorted based on the numbers and not by the chronological orders.
According to the manual (http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format), the DATE_FORMAT() would return “a string.”
Thus, the correct way to sort the date type column is to sort by the original column name. In our case, the original column name was aliased/renamed as “holidayDate.” If we did not alias this, it would have been:
mysql> SELECT date_format(holidayDate,'%m/%d/%Y')
-> FROM holidays;
+-------------------------------------+
| date_format(holidayDate,'%m/%d/%Y') |
+-------------------------------------+
| 05/26/2014 |
| 07/04/2014 |
| 09/01/2014 |
| 11/27/2014 |
| 12/25/2014 |
| 01/01/2015 |
+-------------------------------------+
===> it became "date_format(holidayDate,'%m/%d/%Y')"
Thus my workaround is (I want to use alias!):
mysql> SELECT holidayId, holidayName, date_format(holidayDate,'%m/%d/%Y') 'Holiday Date' -> FROM holidays -> ORDER BY holidayDate; +-----------+-------------------+--------------+ | holidayId | holidayName | Holiday Date | +-----------+-------------------+--------------+ | 1 | Memorial Day | 05/26/2014 | | 2 | Independence Day | 07/04/2014 | | 3 | Labor Day | 09/01/2014 | | 4 | Thanks Giving Day | 11/27/2014 | | 5 | Christmas Day | 12/25/2014 | | 6 | New Year's Day | 01/01/2015 | +-----------+-------------------+--------------+
HP 10BII financial calculator – comma and decimal point swap
This is really funny – one day, I realized that my HP© 10BII financial calculator is displaying a comma instead of a decimal point and displaying a decimal point for a comma…
Very confusing…
The fix is to press “shift”(brown key on the left) and “.” (the decimal point).
The calculator’s user manual is downloadable from the manufacturer’s site: direct link to the manual
/usr/bin/convert.exe: error while loading shared libraries: ?: cannot open shared object file: No such file or directory
I was trying to use the ImageMagick in cygwin to create a jpeg file; however, the execution ended up with the error:
$ convert -size 94×54 xc:white lh.jpg
/usr/bin/convert.exe: error while loading shared libraries: ?: cannot open shared object file: No such file or directory
It seems like some DLL file(s) were missing. To figure that out,
$ cygcheck convert
Found: .\convert.exe
Found: C:\cygwin\bin\convert.exe
Found: C:\cygwin\bin\convert.exe
Found: C:\WINDOWS\system32\convert.exe
Found: C:\cygwin\bin\convert.exe
.\convert.exe
.\cygMagickCore-5.dll
.\cyggcc_s-1.dll
.\cygwin1.dll
C:\WINDOWS\system32\KERNEL32.dll
C:\WINDOWS\system32\ntdll.dll
.\cyggomp-1.dll
.\cygautotrace-3.dll
.\cygming-1.dll
.\cygfreetype-6.dll
.\cygbz2-1.dll
.\cygz.dll
.\cyggif-4.dll
.\cygX11-6.dll
.\cygxcb-1.dll
.\cygXau-6.dll
.\cygXdmcp-6.dll
.\cygpng15-15.dll
.\cygpstoedit-0.dll
.\cygstdc++-6.dll
.\cyggd-2.dll
.\cygfontconfig-1.dll
.\cygexpat-1.dll
.\cygjpeg-8.dll
.\cygXpm-4.dll
.\cygcairo-2.dll
.\cygpixman-1-0.dll
.\cygxcb-render-0.dll
.\cygxcb-shm-0.dll
.\cygXext-6.dll
.\cygXrender-1.dll
.\cygfftw3-3.dll
.\cygfpx-1.dll
.\cygglib-2.0-0.dll
.\cygiconv-2.dll
.\cygintl-8.dll
.\cygpcre-1.dll
.\cyggobject-2.0-0.dll
.\cygffi-6.dll
.\cyggs-9.dll
.\cygidn-11.dll
.\cyglcms2-2.dll
.\cygpaper-1.dll
.\cygtiff-5.dll
.\cygjbig-2.dll
.\cygXt-6.dll
.\cygICE-6.dll
.\cygSM-6.dll
.\cyguuid-1.dll
.\cygjasper-1.dll
.\cygltdl-7.dll
.\cyglzma-5.dll
.\cygpango-1.0-0.dll
.\cyggmodule-2.0-0.dll
.\cygthai-0.dll
.\cygdatrie-1.dll
.\cygpangocairo-1.0-0.dll
.\cygpangoft2-1.0-0.dll
.\cygharfbuzz-0.dll
.\cyggraphite2-3.dll
.\cygicule48.dll
.\cygicuuc48.dll
.\cygicudata48.dll
.\cygrsvg-2-2.dll
.\cygcroco-0.6-3.dll
.\cygxml2-2.dll
.\cyggdk_pixbuf-2.0-0.dll
.\cyggio-2.0-0.dll
C:\WINDOWS\system32\ADVAPI32.DLL
C:\WINDOWS\system32\RPCRT4.dll
C:\WINDOWS\system32\Secur32.dll
C:\WINDOWS\system32\GDI32.dll
C:\WINDOWS\system32\USER32.dll
.\cygMagickWand-5.dll
cygcheck: track_down: could not find cygpng14-14.dll
Installing “linpng: PNG library (docs and demos)” solved the issue. (I had to reboot the machine.)
訃報
日本オラクル社員時代に大変お世話になった木脇高太郎氏がお亡くなりになられたとのこと。
ご冥福を祈ります。どうもありがとうございました。
MSI Sound Effect Manager removal
I’ve personally suffered from MSI’s Sound Effect manager’s weird behavior on Windows 7: the sound effects are on even if they are disabled. Re-installing the drive from Reaktek did not help either. I know the Sound Effect manager is a legacy control panel application, thus it was a possibility this to happen…
Searching on internet did not really help. After suffering almost whole day, I was finally able to fix the problem.
Here’s what I did.
- Disable AC97 device from bios.
- reboot (naturally)
- deleted entries from registry via regedit.exe
- (Make sure you make export of appropriate registry section each time.)
- download PsTools from Microsoft’s Sysinternals.
- run regedit.exe via psexec.exe:
- psexec -s -i -d c:\windows\regedit.exe
- delete following entries in the registory:
- [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Control Panel\Extended Properties\{305CA226-D286-468e-B848-2B2E8E697B74} 2]
- “%SystemRoot%\\system32\\ALSNDMGR.CPL”=dword:00000004
- [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Control Panel\Extended Properties\{305CA226-D286-468e-B848-2B2E8E697B74} 2]
- “%SystemRoot%\\system32\\ALSNDMGR.CPL”=dword:00000004
- [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Setup\PnpLockdownFiles]
- “%SystemPath%\\system32\\drivers\\RTKVAC.SYS”=dword:00000005
- “%SystemPath%\\SOUNDMAN.EXE”=dword:00000005
- “%SystemPath%\\system32\\ALSNDMGR.CPL”=dword:00000005
- “%SystemPath%\\system32\\ALSNDMGR.WAV”=dword:00000005
- “%SystemPath%\\system32\\RTLCPL.EXE”=dword:00000005
- “%SystemPath%\\system32\\RTLCPAPI.dll”=dword:00000005
- “%SystemPath%\\system32\\RtkCfg.dll”=dword:00000005
- “%SystemPath%\\system32\\RtkAPO.dll”=dword:00000005
- “%SystemPath%\\system32\\RtkPgExt.dll”=dword:00000005
- [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\AppCompatFlags\Layers]
- “C:\\Windows\\System32\\ALSNDMGR.CPL”=”RUNASADMIN”
- [HKEY_USERS\S-1-5-21-3750584346-3454113483-1130964700-1000\Software\Microsoft\Windows NT\CurrentVersion\AppCompatFlags\Compatibility Assistant\Persisted]
- “C:\\Windows\\System32\\ALSNDMGR.CPL”=dword:00000010
- [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Run]
- “SoundMan”=”SOUNDMAN.EXE”
- and deleted the files mentioned in the deleted registry values.
After this, restart the computer and re-enable the AC97. Windows should automatically re-configure the sound device.
Good luck!