Press "Enter" to skip to content

Category: Projects

Code Snippet: Extracting a 24-hour time from a 12-hour time string in MySQL

The snippet:

HOUR ( STR_TO_DATE ("2:30pm" , "%h:%i%p") ) = 14

Why I needed it:

I’m currently working on a time and date filter for ClassGet. If you’ve ever wanted to find a morning class that meets only on Tuesdays, this tool is for you! The main problem, however, is that Furman’s course listings file has the start and end dates for classes in this format:

Start Time: "12:30pm", End Time: "1:20pm"

and my import script doesn’t convert these into a DATETIME format in SQL. I want to be able to look at courses from If I want to find a class starting from 12:00 to 14:00, I’ll need to do some conversion. On the front-end, I’m using a jQuery UI slider for my hour control that goes from 5 (5:00 am) to 21 (9:00 pm). Why anyone would have a class starting at 9:00 pm is beyond me, but hey, it could happen. I’m not going to worry about minutes, and I know that no class will ever be offered overnight so I’ll never need to worry about a start time being later than an end time. My script will search for classes that start from one hour to another, so I’ll need to convert the dates to match. You would think I could just do something like this:

SELECT * FROM classes WHERE `Start Time` BETWEEN 12 AND 15

But it doesn’t work like that. The dates are stored as strings, e.g. "2:30pm". We’re lucky, though… MySQL has an HOUR function that will solve that!

SELECT * FROM classes WHERE HOUR(`Start Time`) BETWEEN 12 AND 15

But still, no classes are showing up that start at 2:30. How come? As it turns out, HOUR("2:30pm") returns 2, not 14! How do we fix that? The answer lies in MySQL’s STR_TO_DATE function, which is the reverse of the DATE_FORMAT function. Now, take a look at the final version:

SELECT * FROM classes WHERE HOUR(STR_TO_DATE(`Start Time`, "%h:%i%p")) BETWEEN 12 AND 15

There we go! Now without doing any PHP or JavaScript or modifying the database structure, I was able to create a date filter for class data.

Weekend Project: Video Poker in C# and Silverlight

I’m working on building an electronic piggy bank style box like this one I’ve seen that will count my spare change, and I wondered if I could make it more interesting. I thought back to coin-operated arcade machines and thought that I could make a pretty cool video poker software that would read my piggy bank and let me play poker with my change as well. I decided to learn a new language to do it: Microsoft’s C# (sharp).

A few good friends of mine use C# in their jobs, and its an interesting choice because it’s a .NET language and the same code can be used on the Windows desktop and online using Microsoft Silverlight.

I ported some old Python code I had over to C# to learn about what makes it different, and after a bit of searching through the Microsoft Developer Network (MSDN) I found out the correct ways to do some things, and after some more tinkering and learning WPF, the Windows Presentation Format, I had my application.

Now, I don’t want to break any gambling laws so all the “credits” earned are 100% virtual, and have no cash value. In the future I can wire this up to the coin acceptor I’m building to accept more credits into the machine, but once they’re there, they stay there. I did make a rudimentary user account system that saves ones balance in-between sessions, so I can reboot my computer and I can still play later.

I still need to learn how to cache the card images somewhere, because every time I draw the cards the images are pulled from my server again and again.

To the web!

As I said, I’ve heard that Silverlight makes it easy to cross-compile applications for the web. It’s true for the most part, but there are some discrepancies. First, some controls such as the Label that work in WPF don’t work in Silverlight. Also, any Class Library DLL files that you’ve compiled for the desktop won’t be able to be added to Silverlight, so make sure if you’re compiling for both that you save your .cs files!

I make a few changes to my application since that screenshot was taken, and have made a web-playable version. This lacks user accounts, and you’re awarded 100 credits every time you visit the page! If you go below 0 credits, I don’t know what happens, but I think you just go into the negatives. It’s a prototype, and it’s controlled entirely via the keyboard for now. Make sure to click the blue background before using the keyboard to make sure the program has focus! I might improve it later, but my focus is on the desktop implementation for now.

I’ll be on Spring Break for the next week, but I’ll update once I get back!