Postgres SQL to see disk usage for tables

Raspberry Pi B+ Garage Door Opener


Here is the source for this project
This is my system designed around a Raspberry Pi B+, 2 magnetic overhead door contacts, and 2 5-volt relays. Wired into a 2 car garage this makes an internet enabled garage door opener/monitor that can be accessed from anywhere.


* Raspberry Pi B+ kit
* overhead door contacts (one for each door)
* 5-volt relay (one for each door)
* NEMA or other enclosure to mount everything
* 18/4 wire (or some other low voltage wire)
* 2 Terminal blocks
* Various connectors to wire the devices together – Ribbon and jumpers
* Screw drivers and tools

Hardware Setup

1. Overhead Door Contacts

My door contacts needed a 2.5 inch or less gap to work. This made it easy to mount the sensor (the part with the wires) on the wall at the top corner of the door and to put the other part of the contact on the top corner of the door itself. Since there is only about a 3 foot space between the doors,

I mounted the right door’s contact on the upper left side and the left door’s contact on the upper right side. This meant that the 2 wires from each contact could meet in the middle and I could use 1 strand of the 4 conductor wire to get both contacts back to the NEMA box.

Top left contact mount
Top left contact mount
Closed door contact location
Closed door contact location

2. Opener Button Wiring

My door switch is on the inside wall, and is simply a contact closure switch that causes the motor to start going up or down. This is pretty simple, and it means that a relay is the perfect way to “push” the button with the Pi. To do this, we have to have 2 wires (or half of our 4 wires in the cable) wired to the NO side of the relay so when we send a signal to the relay it closes the loop and “pushes” the button.

There is a terminal strip on the back of the opener with 4 screw-in terminals. The wireless remote and door switch are already run to these terminals, and the 2 inside terminals are the ones that we want. I checked this by taking a screwdriver and shorting these 2 points; this caused door to open just like we’d pushed the button.

This means we have to run wire from the opener on the ceiling to the location of our equipment, just like the contact wiring. Since the openers are about 5 feet apart, I ran one wire to each unit even though I only needed half of the 4 wires.

Opener wiring
Opener wiring

3. Putting it together

Now, we mount the components on a board and put this into the NEMA box. Then, we pull the wires into the box and hook everything up. I used one inch pegboard to put the components on, but anything that closes to keep the dust out is good. The only issue with using an all-metal box is that the wireless does not work inside the box (I guess it’s a Faraday cage or something).

Connect the Pi to the relay

  1. Mount the 2 terminal strips, Pi board, 2 relay board and ground post to the board.  From the Pi, run the following pins to one of the terminal blocks:
    • The 5V pin
    • A ground pin
    • Two (2) GPIO pins for the Pi to use as to “push” the button
  2. Run the following wires from the relay to the terminal block to match the ones from step 1
    • The “VF” side of the relay coil goes to the 5V pin
    • The ground side of the relay goes to the ground Pi pin
    • The 2 relay coil activation pins to the GPIO pins, noting which is which (i.e. right door to GPIO 5)


At this point, the Pi should be able to control the relay.  My relay is activated when the activation pin on the relay goes low.  In other words, the GPIO pin connected to the relay should be ON unless we are pulsing the input.  This means that to “push” the button we start with the relay ON, when we want to push the button we turn the GPIO off, wait .2 seconds and then turn it back on.

Assuming your switch is on pin 3, here is how you would do this in python:

Now, we don’t have the other side of the relay hooked up yet, but you should be able to hear the relays click when the switch is activated.

Connect the relay to the switches

Locate one pair of wires coming from one of the opener units. You can check the wires because if you have the correct pair you can touch them together to short them and the door should close or open.

Hook these wires up to the terminal block on one side, and hook up one of the relay common and NO pins on these. This means that when the relay is activated the NO and common pins will be shorted and the motor should start. If you have it hooked up correctly, you should be able to run your pulse script again and the garage door should work!

Do this for the other pair of wires for the other opener and the other relay.

Connect the door contacs

The door contact should be hooked up with one wire going to the ground, and one wire going to a GPIO input.  The input is setup with a PULL_UP resistor which means that you will get a 1 (or True) when the contact switch is closed, and a 0 (or False) when the contact switch is open.

Setup software

To create your SSL keys for the app to use, once you’ve setup the software on your pi and created the */etc/garage* directory, you can run:

This will setup your SSL keys so you can use https, which you want to do if you’re going to setup port forwarding and enable this from the Internet.

Checkout door code, configure json file of doors, create ssl keys and make sure you have all the required packages.  Run the file and then connect to https://raspberr-pi to see the site:

One Open Door
One Open Door
Both Doors Closed
Both Closed

SQL Server int datetime rounding errors

Demonstration of errors when casting between float and datetime in SQL Server.


SQL Cast Example Data