What’s this thing called “Current Use”?

Posted on 18 June 2019 by Rich Brown

The Current Use law in New Hampshire provides a tax break for certain property owners who choose not to change the “current use” of their property to a higher level of development. However it shifts the tax burden to property owners who do not have property in “current use”. Here are some details.

In 1973, the NH Legislature created the Current Use law to “encourage the preservation of open space” by giving property owners a break on their taxes. (NH RSA 79-A:1)
The NH Current Use Board sets the level of “encouragement”. Today, current use landowners receive as much as a 98% discount on their property taxes.
This means that the other residents in town have to pay higher property tax to make up for the taxes not collected from properties in “current use”. (It’s a zero-sum game. If the legislature mandates a discount on taxes for some properties, the taxes have to go up for other residents.)
Many property owners have already permanently conserved land. Consequently they do not need additional encouragement (through a tax break) to preserve open space since development is already precluded.
In my town of Lyme, NH, it appears that 94% of all land in Lyme is either conserved or in current use. Discounts for current-use properties total over $1.5 million and add about $5 to our $27.19 per thousand tax rate.
Forty-five years later, we now understand the true effect of the Current Use law: in rural towns with lots of open space, it drives up the tax rate for all residents. The law has relatively little effect in more densely-developed, urban towns, since there are few current use properties.

The NH Current Use Board has opened a public comment period (for this year) about the proposed Current Use Assessment Ranges. Comments may be submitted in writing to Tracey Russo, Paralegal Department, NH Department of Revenue Administration by mail at PO Box 457, Concord, NH 03302; by fax at (603) 230-5932; or by e-mail at Tracey.Russo@dra.nh.gov. The deadline for the submission of written comments is Thursday, June 20, 2019.

I just sent an email to Tracey.Russo@dra.nh.gov along the lines below:

To the NH Current Use Board:

I am a resident of Lyme NH. I wish to submit a public comment to the Board regarding their regulations regarding Current Use.

Sincerely,

/s/ Your name

The NH Legislature could rewrite the Current Use law to make it more equitable. This is a long term project. However, I request the Current Use Board to consider the following changes to their regulations:

1) Add the requirement that land may not qualify for current use treatment if it is already has a permanent restriction from development.

2) Decrease the base discount on Current Use property, to create meaningful incentives for creating a stewardship plan or making the land available for the public to access.

3) Set different discount rates for current use land and permanently-conserved land. Permanently conserved land can be assessed at its market rate, while current use land can receive a temporary discount for the time that the land is in its current use.

I would encourage you to send your own message, using any (or all) of the points above. I would also like to know what you wrote: you can cc me at richb.lyme@gmail.com. Thanks!

Feel free to share this post on Facebook, LinkedIn, Twitter, or email by clicking one of the icons below. Any opinions expressed here are solely my own, and not those of any public bodies, such as the Lyme Planning Board or the Lyme Community Development Committee, where I am/have been a member. I would be very interested to hear your thoughts – you can reach me at richb.lyme@gmail.com.

Strong Towns “Strength Test”

Posted on 18 June 2019 by Rich Brown

I was browsing around the Strong Towns web site, and came upon their criteria for measuring the strength and resilience of a town. They point out that full technical analysis is time consuming and often not usable by non-technical people.

So they designed a ten question survey at https://www.strongtowns.org/journal/2014/11/15/strong-towns-strength-test My favorite question is #3:

3. Imagine your favorite street in town didn’t exist. Could it be built today if the construction had to follow your local rules?

I would be intrigued to hear your thought/answers. Send them to me at richb.lyme@gmail.com. Thanks.

Strong Towns – Breaking out of the Housing Trap

Posted on 5 June 2019 by Rich Brown

On 2 April 2019, I went to a talk sponsored by New Hampshire Housing Finance Authority in Claremont, NH. The announcement stated:

What does it mean to be a “Strong Town”? In April, nationally known planner and engineer Chuck Marohn will speak at community events in Rochester, Concord, Claremont and Portsmouth to discuss the STRONG TOWNS perspective of community economic resilience, citizen involvement, and land use.

Marohn will focus on how housing affordability fits with the STRONG TOWNS philosophy during the April 1-2 events presented by New Hampshire Housing Finance Authority.

Here are my notes from the talk. (Does this seem too long? Don’t want to read the whole thing? See the summary…)

The speaker was Charles Marohn from Brainerd MN. He’s the founder of StrongTowns.org, and has been working since 2008 to create models for towns to become financially strong and resilient.

High Price of Housing

He observed that cities and towns are gentrifying – housing prices are going up – and that stagnating incomes everywhere mean that people have trouble affording to live there. He is seeking improvements that will stick, and leave the municipality in a good place.

He talked about the “Agriculture Trap”. Once a civilization gets a surplus in resources (because of agriculture), people had the ability to specialize and spend time on other pursuits – to become shamans, soldiers, artists. The increased resources also meant that the population could increase.

The “trap” is that the increase in population forces the need for more food, which increases the reliance on agriculture, which allows further population growth. That civilization is “trapped by agriculture” – it can never go back to its pre-agricultural state.

The Housing Trap

Marohn traced the history of the current “housing trap” to the way governmental and financial systems make it hard to change the way homes are funded. The Great Depression created massive deflationary pressure, and led to the Homeowners Act of 1933. Fannie Mae was created in 1938, to securitize home mortgages. The GI Bill was a good program, but caused home prices to rise. Freddie Mac quasi-privatized all this in the 70’s.

Which means that to have a middle-income home today, almost every family must have two breadwinners.

It also means we cannot (easily) go back to lower-price housing because of the structural problems. Who benefits from high housing prices?

– Local, state, and federal government (property and other taxes)

– Baby Boomers – who rely on the high cost (value) of their homes as a significant portion of their retirement

Complex vs. Complicated

Marohn talked about the difference between “complex” and “complicated” systems.

Complex systems are adaptive, and adaptable. They emerge from good attributes because changes to the environment can be absorbed and adjusted to.
Complicated systems are predictable, but fragile. If things go badly for a complicated system, they break. He gave the example that a farm is complicated. If it’s a bad year for corn, the farmer cannot simply plant beets.

Cities/towns are the quintessential human habitat. They are also complex: they emerge from a collection of interacting objectives, they experience feedback, and adapt to those pressures. This is good – it leads to resiliency to challenges and stress.

Summary

How can a town improve and thrive? Marohn offered these strategies:

1) Allow the next increment of building style by right. If the town currently allows single family homes, allow accessory dwelling units. If your town allows duplexes/accessory dwellings, allow quads. This must be *by right* – no special permissions should be necessary. However:
– No neighborhood should experience radical change, and
– No neighborhood should be exempt from change

Towns should look toward dynamic rezoning. As the town changes over the years/decades, so too should the zoning rules adapt to what people are requesting.

2) Lower the bar to entry. To encourage people and businesses to move to town, find a way for people to “start with nothing, and end with something.”

For housing, most people can’t imagine living in a 500sf home. Yet there are countless homes in our communities that obviously started out very small, but expanded as their owners had more resources. Zoning should allow people to start with very small residences affordably.

For business, don’t require an entrepreneur to meet every strict requirement. (If a building needs $300K of improvements to open, it will never happen.) Instead, correct the imminent health threats, and then give a provisional occupancy permit. (Maybe even put a sticker on the front window stating the provisions of the code that aren’t currently met.) Then after six months of operation, rank things. Require the owner to put 3% of their revenue annually into an account for property upgrades. When all the conditions are cleared, everyone can have a celebration. In the meantime, the property is providing services, on the tax rolls, and getting better.

3) Respond to how people use and live the city/town. Marohn advocated this public investment process for a strong town:

Humbly observe where people are struggling
Ask “What is the smallest thing we can do to address that struggle?”
Do it. Right now.
Repeat

Marohn closed by noting there is a lot more information on the Strong Towns (https://strongtowns.org) website.

Can Accessory Dwelling Units Help Meet Our Region’s Housing Needs?

Posted on 29 May 2019 by Rich Brown

On 4 October 2018, PlanNH and Vital Communities sponsored a talk titled, Can Accessory Dwelling Units Help Meet Our Region’s Housing Needs? at the Kilton Library, in West Lebanon NH.

The primary speaker was Kol Peterson from Portland Oregon. He has published many books/resources on the subject of Accessory Dwelling Units: http://BuildingAnADU.com, https://accessorydwellings.org, and Backdoor Revolution.

His answer is a strong, YES. Primary drivers for people to create an ADU:

Rental income
Flexible space for intergenerational use
Age in place

Why are ADUs valuable for a municipality?

75% of 2018 households have 1-2 people
In major cities, 35-45% of households have a single person
But they have no option but to purchase a large single family home
Average home: 1950 – ~950sf; 2018 – ~2600 sf
Small homes cost less to build, and have HUGE energy savings
A huge benefit of additional housing using existing homes with minimal impact to the town/streetscape.

Parking: In Portland OR, the US city with greatest penetration (1%), ADUs have no observable effect on parking. (A municipality only needs to find space for one additional car per hundred homes.)

The Finances: ADUs (like all construction projects) are expensive to build, therefore, breakeven has multi-year payback:

250sf => $100K
600sf => $200K
750sf => $150K to $300K

ADU Construction is not financially viable for ordinary developers who want to “build-and-flip”:

Doesn’t improve the value of the property enough to make up for the $100k-300K construction cost.
But… rental income breaks even over 5-10 years (depending on construction cost and market)
Attractive for people who plan to stay in the neighborhood for 20-30 years

Financing is hard: must rely on home equity line of credit, savings, cash out of liquid assets, family, sweat equity (often 50% of cost)

Takeaway: Given the difficulties of building anything (brainstorming, finding an architect, talking with the neighbors, schematics and design, etc) and the modest financial returns, ANY limits of permitting and zoning regulations discourage people from developing ADUs. There is a good discussion in the slides. Another example of a disincentive, the State of NH requires an attached ADU to have a connecting door.

Furthermore, many laws/ordinance/regulations require owner-occupied ADUs. This is also a disincentive, for the following reasons:

Appraisers will undervalue because of deed restriction, therefore may not be able to refinance
Lenders cannot (by definition) owner-occupy, so may choose not lend
Affordable housing NGOs cannot occupy, so may not fund either
The requirement places a significant restriction if the owner needs to move
Why wouldn’t owner simply build an un-permitted ADU?
No cities have seen large number of ADUs, so it’s not a practical problem
Finally, what other property type in the US has this requirement?

However, certain restrictions are not unreasonable:

Regulations limiting ADU to 750-800sf are not an important restriction because ADUs are designed for secondary home/smaller unit
Regulations preventing ADU from being subdivided is not important restriction

What about people who use ADUs as Short Term Rentals?

There’s no data on the trends
If it is becoming a problem, decouple ADU reg’s from ST rental reg’s – they’re not the same thing.

Slides: https://vitalcommunities.org/wp-content/uploads/2018/10/Kol-Peterson-Slides-Lebanon-NH-Oct-2018.pdf

Video: https://www.facebook.com/bmlgreenguy/videos/vb.1523616449/10217649924829603/

Sponsors: Vital Communities – Workforce Housing and PlanNH

Jetpack Publicize: Setting the image and text shown on Twitter and Facebook

Posted on 29 May 2019 by Rich Brown

I use Jetpack Publicize to share my posts on Facebook and Twitter. This makes it easy to create a single WordPress post and have it show up on social media sites. Those services automatically use the Featured Image of the WordPress post, plus an excerpt of the title/text.

There’s another cool feature: Facebook and Twitter will include a photo and text summary for your site whenever someone types your URL into their post on Facebook and Twitter.

But… It can be tricky to figure out how Facebook/Twitter retrieve the image and the text. (I started this quest because Facebook was choosing the wrong image for the thumbnail. I found my first clue by reading WP Beginner’s article How to fix Facebook Incorrect Thumbnails.) This led to the debugging techniques below.

Both Facebook and Twitter use Open Graph metatags to find the desired image, text, title, etc. Jetpack automatically sets these OpenGraph meta-tags: og:image tag, og:title, and og:description. It also automatically inserts tags for Twitter: twitter:text:title and twitter:description.

To see what those services will display, each has a Debugger/Validator. To use them, go to the page below and type in your URL.

Facebook Validator: https://developers.facebook.com/tools/debug/sharing/
Twitter Card Validator: https://cards-dev.twitter.com/validator

Problem: Jetpack automatically sets the OpenGraph tags, but sometimes it chooses values that are not useful, and they cannot be modified within Jetpack. (At least, Jetpack support could not tell me how to set them.)

Update: Using Jetpack 7.3.1 and Yoast 11.3, the conflict between these two plugins appears to have gone away. But this note still describes useful debugging techniques for Facebook and Twitter. (31May2019)

Solution – Part 1: The Yoast SEO plugin – I use the free version – also lets you set the Open Graph tags for both Facebook and Twitter. (In addition, I really like how it helps to optimize my text for search engines.) However, Yoast and Jetpack’s Open Graph tags interfere with each other.

Solution – Part 2: ~~This is a little bit yucky, but you can disable Jetpack’s OpenGraph tags with a bit of code in your child theme’s functions.php file, as described in Remove Jetpack’s Open Graph meta tags~~ This is no longer required – see Update above.

TL;DR: (Too Long; Didn’t Read)

Use Jetpack Publicize to share your posts on social media
Use Yoast SEO to set the Open Graph tags for Twitter and Facebook
~~Disable Jetpack’s Open Graph tag facility – Jetpack will still share the posts, but won’t add its own Open Graph meta-tags~~ This is no longer required – see Update above.

RandomNeuronsFiring.com – now live!

Posted on 19 May 2019 by Rich Brown

I have reworked my blog so that the primary domain name is “Random Neurons Firing” (instead of the pedestrian richb-hanover.com). Same content, but a better name.

I’m also adding a new topic to those I’ve previously covered (“Software, Networking, Life”). Over the last two years, I have gone to many planning and zoning conferences to learn more about how to provide attractive housing within communities. I’ll post my notes from those conferences and workshops here. I need to note that these will be my own opinions, and not those of any public boards to which I might belong.

Spring 2019 Business Leaders Housing Breakfast

Posted on 13 May 2019 by Rich Brown

On May 3, 2019, I attended the Spring 2019 Business Leaders Housing Breakfast sponsored jointly by Vital Communities and Twin Pines Housing. Twice a year, these groups bring together a group of speakers to address questions about housing. I have included links to the three presentations. Here are my takeaways:

Bennington Healthy Homes

Kevin Dailey of Southwestern Vermont Health Care (SVHC) described how potential employees have trouble finding housing close to the hospital and consequently endure a long commute. SVHC established a program to acquire abandoned homes that are not economically viable as a commercial renovation. They then upgrade the homes so that there will be no major expense for 10 years, then pay the closing costs for an employee to purchase. They expect to spend about $25,000 per home. They have done four homes so far.

Woodstock Housing Initiative

Jill Davies spoke about how Woodstock Community Trust established a program to help moderate income people live in Woodstock, Vermont. They point out the need by asking these questions:

Who’s saving your life?
Who’s teaching your kids?
Who’s cooking your food?
Can they live in your community?

The Housing Initiative is about to sell their first home, working with Twin Pines Housing to help home buyers using a model developed on Martha’s Vineyard:

A buydown program, where the initiative puts up the down-payment, mortgage insurance, closing costs, to help a person who can afford the monthly payments, but doesn’t have the cash for up-front costs
They make structural repairs and fix major appliances, to avoid big bills in the first three years

Upper Valley Real Estate Update

In their semi-annual review of real estate housing trends, Buff McLaughry of Four Seasons Sotheby’s International Realty and Lynne LaBombard of LaBombard Peterson Real Estate LLC gave these highlights:

They consider the Upper Valley to have 69 towns, about 187,000 population, and 87,000 jobs.
Affordable housing is a core component of community health. If housing isn’t readily available, the community is suffers.
Rental property across the Upper Valley have less than 3% vacancy (very low), this is down about 10% from a year ago.
The commute to jobs has not changed much in the last year, but remains high at about 45 minutes.
Number of home sales has remained roughly constant over the last year, but inventory has changed:
- Homes below $300K: inventory is strongly down – hardly any are available
- Homes between $300K and $600K: down, less available
- Homes above $600K: about the same, or slightly higher inventory

2019 Homeownership Conference – NHHFA

Posted on 20 March 2019 by Rich Brown

On March 19, 2019, the New Hampshire Housing Finance Authority held its 2019 Homeownership Conference. Agenda is at: https://www.nhhfa.org/assets/pdf/HO_Conf_3-19_Agenda.pdf

How can we get more homes built? How can we keep pace with the demand?

Dean Christon – Executive Director of NHHFA pointed to several initiatives:

St. Anselms College Center for Ethics in Business & Governance

Need more funding
Need a state-wide housing board
Need a legislative study on housing density

There are three interesting bills in the legislature and their sponsors:
SB 306 – Housing Appeals Board – Giuda
SB 15 – Affordable Housing Fund – Bradley
SB 43 – Density Study – Fuller-Clark

Finally, he introduced the NHHFA 2019 Housing Market Report Read it at https://www.nhhfa.org/assets/pdf/NHHFA_HMR_3-19_final.pdf

Improving Housing Affordability

Elliot Eisenberg (the “Bowtie Economist”, Econ70.com) gave an entertaining and insightful review of housing issues. His major points:

The yield curve has inverted. This is a symptom of a non-zero chance of recession but at least 12 months out
There is a slowing economy, both in the US and the world. This is leading to slow growth
Regulatory costs add about $20,000 to $30,000 to the typical home. Mostly these are costs of complying with zoning
There are labor supply problems – builders cannot attract qualified builders, driving up labor costs
Land costs are high, making it more expensive to build
Builders have stopped building homes below $250,000
Rent growth has slowed recently
Municipalities should practice dynamic zoning. We regularly rezone commercial and industrial properties, but rarely do we allow changes to the density of residential districts
We must get housing density up, to decrease the cost of homes

He gave a similar presentation to the Greater Houston Builders Association Conference two weeks prior. That video is at: https://www.youtube.com/watch?v=Jv1do15Yx_Q

New Hampshire’s Housing Needs

Russ Thibault, Applied Economic Research, gave a talk about the New Hampshire housing needs. High demand for rental housing – “If the paint is dry, it’s occupied.” There is a shortage of 15,000 to 20,000 units in NH, with a demand for about 3,500 new units per year.

However, new construction is lagging because of high prices for construction materials (especially lumber), high labor costs (2.5% unemployment makes it hard to find qualified carpenters), and high land prices mean that existing housing is a relative bargain, driving up those prices.

He also gave a lesson in the history of zoning: In the 1980s, there was a sense of unease that the entire state would become overrun because of development pressure from Massachusetts. Ordinances were put in place to create a regulatory framework to prevent towns (especially in the southern part of NH) from being overwhelmed. These laws succeeded, but are now too restrictive and keep towns and municipalities from developing reasonable housing alternatives.

Engaging Stakeholders to Find Solutions

A panel discussion with the following people:

Builder: KEVIN LACASSE, New England Family Housing
Realtor: MATT MERCIER, Jill & Co. Realty Group
Architect/Engineer: TIM NICHOLS, AECm
Banker:PETER RAYNO, Enterprise Bank
Local Government: STEPHEN BUCKLEY, NHMunicipal Association
Moderator: BEN FROST, New Hampshire Housing

I took a few notes:

Mixed use development is good: Single family or rental above office or retail on the first floor.
Modular/pre-engineered construction helps improve labor cost/schedule/quality, with less waste, environmental impact, and bigger (annual) energy savings.
Riggins Rules – a good framework for being an effective land use board.

Summary of the Day’s Discussion

Ignatius Maclellan, Managing Director, Homeownership Division of NHHFA offered these thoughts:

Fully fund the State of NH Sewer Expansion work.
There are planning grants that can mitigate developer risk
To get market-price homes, streamline approval processes

No on Lyme’s Article #2

Posted on 10 March 2019 by Rich Brown

Background: In March 2019, the Planning Board proposed an amendment to the Lyme Zoning Ordinance to modify the current Lot Size Averaging provision. I sent the following notes to the Lyme Listserv to ask residents not to vote for this change.

Update: At Town Meeting, Article 2 (the Lot Size Averaging amendment) failed by a vote of 282 votes against, and 198 votes for it.

To the Lyme Listserv,

I am writing because a couple people asked my opinion of the Lot Size Averaging (LSA) amendment (Article #2) on Tuesday’s warrant.

Lot Size Averaging (LSA) could provide a useful tool but as amended, it dramatically decreases what is allowed in comparison with what an owner could build in a conventional subdivision. The details make for a long message, but in sum it permits much less in the way of house (about 50% less) and outbuildings (up to 80% less).

Furthermore, the “procedural simplicity” claimed for the new language comes by removing the right of an applicant to appeal to the zoning board for relief from the new restrictions.

Given these new constraints on what can be built, this proposed LSA amendment doesn’t provide value to Lyme, or to a resident who would consider using it.

That said, smaller, more dense (and less expensive) housing can help those who want to downsize and stay in town. It also helps the many people who serve the residents of Lyme in our schools, homes, restaurants, and who provide services such as hairdressers, firefighters, nurses, and electricians. They should be able to live in Lyme, too.

If you think that alternatives should be allowed in Lyme, please vote No on Article #2 on the paper ballot this Tuesday, and encourage our dedicated volunteer planners to return to the drawing board and consider other strategies for housing.

Rich Brown

In a followup email, Mary Callahan wrote:

Can anyone give me more information on this article? I’ve read the article on the town report but I’m wondering about the background. How does this differ with what’s in place now and what is the need that the board sought to address with this amendment? Maybe someone can point me to minutes on the town site, I haven’t been able to find them.

Thanks for your curiosity. As I noted in my previous message, Lyme has a complicated ordinance regarding how people can build on their land. Since our application for subdivision triggered this amendment, let me use our property to illustrate how the new language constrains the ability to build homes.

To summarize, our 96-acre parcel at the Loch Lyme Lodge yields only four buildable lots under the both the current and new language. However, the new language would limit our building footprint by more than 50%, the lot coverage by 80%, and the gross floor area by two thirds from what could currently be allowed on the parcel.

The Details

The Lyme Ordinance promotes single-family homes on large lots. A “buildable lot” must have at least three acres remaining after subtracting conservation overlays – wetlands and buffers, agricultural soil, steep slopes. Parent parcels frequently need to be twice or three times as large (six to ten or more acres) to meet that minimum lot size.

Under Current Ordinance:

Pinnacle Project owns a 96 acre parcel in the Rural district at the Loch Lyme Lodge that could be used for residential purposes. After subtracting conservation overlays and applying the three/five acre rules, the 96 acre parcel ends up with only four “buildable lots” of 3.0, 5.6, 6.8, and 8.6 acres, respectively. These lot sizes determine the Building Footprint, Lot Coverage, and Gross Floor Area for the homes.

Building Footprint – In a conventional subdivision of our land, the current ordinance would permit home footprints that are 2% of the lot size, or 2,622, 4,855, 5,949, and 7,000 square feet for a total of 20,426 sf.

Maximum Lot Coverage limits the total square footage of the home, garage, barn, workshop, greenhouses, sheds, outbuildings, etc. In a conventional four-lot subdivision the allowance is a generous 12% of the lot size, capped at 26,000 sf. Three of our lots would allow 26,000sf, the fourth would be 15,734 sf of lot coverage, for a total of 93,734sf.

Gross Floor Area in the residential district is capped at 14,000 sf. In practice, the height limitations mean that the homes likely would have two stories, and our buildings would max out at 5,244, 9,710, 11,898, and 14,000 sf, a total of 40,852sf.

I acknowledge that these would be large homes, but if someone wanted to build them, they would be permitted automatically (subject to suitable septic, driveway, etc. plans) under the current ordinance. I also note that the setback requirements and other dimensional controls would require that the homes be spread out across the property with no protection of open space.

Using the New Lot Size Averaging language:

If an applicant instead wanted to avoid breaking up the open space on a parcel and cluster the homes, the new ordinance applies severe constraints on these homes. Using the same lot sizes for our property:

Building Footprint: limited to 2,500sf per home. Four homes would total 10,000sf, or less than 50% of what is currently allowed.

Maximum Lot Coverage: limited to 4,500sf. Four homes would total 18,000sf, or only 20% of what would be allowed.

Gross Floor Area: limited to 3,000sf. Four homes would total 12,000sf, or less than a a third of what would be allowed currently.

Two More Points:

The current ordinance also limits building sizes for lot size averaging, but includes an option to go to the Zoning Board to request for relief to those constraints. The new language explicitly removes this option.

Finally, the building limits seem arbitrary. During both the drafting hearings and the public hearings, the board set the footprint and gross floor area sizes by their feelings that they were “reasonably sized” and “generous” homes according to their “perception of what’s reasonable.”

Summary: The proposed language is a disincentive for the use of Lot Size Averaging. In the only recent case, this new language would limit the footprint by more than 50%, the lot coverage by 80%, and the gross floor area by two thirds over what could currently be allowed.

This is not a useful change, and only continues the Ordinance’s promotion of single family homes on large lots.

If you agree, please vote No on Article #2 and encourage our dedicated volunteer planners to consider other strategies for housing.

SQLite Date and Time Functions – explained

Posted on 28 February 2019 by Rich Brown

A while back, I was greatly confused by SQLite date and time functions. It took me a while to figure out what was wrong. (It was my error: I hadn’t observed the rule that dates must have this form “YYYY-MM-DD” – four digit year, two-digit month and day.)

Nevertheless, I found that the documentation wasn’t quite clear, so I wrote up these notes as an adjunct to SQLite Datatypes and the SQLite Date and Time Functions pages.

2.2. Date and Time Datatype

SQLite does not have a storage class set aside for storing dates and/or times.
The conventional way to store dates is as a string in a TEXT field.
These fields can be compared directly (as strings) to determine equality or order.

For other date-as-string formats, see Date Strings on the Date And Time Functions page.

For further manipulations on dates and times, the built-in Date And Time Functions of SQLite convert dates and times between TEXT, REAL, or INTEGER values:

TEXT as strings (“YYYY-MM-DD HH:MM:SS.SSS” – with leading zero where required, and four-digit year – a so-called “timestring”)
REAL as Julian day numbers, the number of days (with fractional part) since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

Applications can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.

Date And Time Functions

The Date and Time Functions page doesn’t really define the the arguments or the return types, so I make them explicit below.

Timestring: The conventional way to store dates is as a timestring – a TEXT field (e.g., “YYYY-MM-DD HH:MM:SS.SSS”). These fields can be compared directly (as strings) to determine equality or order.

To convert to other date representations, SQLite supports five date and time functions. All take a timestring (a subset of IS0 8601 date and time formats, listed below) as an argument. The timestring is followed by zero or more modifiers. The strftime() function also takes a format string as its first argument.

date(timestring, modifier, modifier, …) Returns the date as a string: “YYYY-MM-DD”.
time(timestring, modifier, modifier, …) Returns the time as a string: “HH:MM:SS”.
datetime(timestring, modifier, modifier, …) Returns a string: “YYYY-MM-DD HH:MM:SS”.
julianday(timestring, modifier, modifier, …) Returns the Julian day as an REAL – the number of days (and fractional part) since noon in Greenwich on November 24, 4714 B.C. (Proleptic Gregorian calendar).
strftime(format, timestring, modifier, modifier, …) Returns the date formatted according to the format string specified as the first argument. The format string supports the most common substitutions found in the strftime() function from the standard C library plus two new substitutions, %f and %J.

… see the original SQLite page for modifiers and legal timestring formats …

Examples

This section replicates the examples of the original page, but includes the results and types of the function.

Compute the current date. Returns timestring.

SELECT date('now');  -- Result: 2018-03-07

Compute the last day of the current month. Returns timestring.

SELECT date('now','start of month','+1 month','-1 day'); -- Result: 2018-03-31

Compute the date and time given a unix timestamp 1092941466. Returns timestring.

SELECT datetime(1092941466, 'unixepoch'); -- Result: 2004-08-19 18:51:06

Compute the date and time given a unix timestamp 1092941466, and compensate for your local timezone. Returns timestring.

SELECT datetime(1092941466, 'unixepoch', 'localtime'); -- Result: 2004-08-19 14:51:06

Compute the current unix timestamp. Returns INTEGER.

SELECT strftime('%s','now');  -- Result: 1520444198

Compute the number of days since the signing of the US Declaration of Independence. Returns REAL – days and fractions of a day.

SELECT julianday('now') - julianday('1776-07-04'); -- Result: 88269.7339379285

Compute the number of seconds since a particular moment in 2004: Returns INTEGER.

SELECT strftime('%s','now') - strftime('%s','2004-01-01 02:34:56'); -- Result: 447519729

Compute the date of the first Tuesday in October for the current year. Returns timestring.

SELECT date('now','start of year','+9 months','weekday 2'); -- Result: 2018-10-02

Compute the time since the unix epoch in seconds (like strftime(‘%s’,’now’) except includes fractional part). Returns REAL – days and fractions of a day.

SELECT (julianday('now') - 2440587.5)*86400.0; -- Result: 1520444280.01899

A Practical Tutorial for SQLite Date Functions

The SQLite document doesn’t really show how to use date functions in actual code. Here is an example of inserting and retrieving dates in a table.

Note: It is good practice to store dates as text in a datestring format – YYYY-DD-MM. The “BMW” entry below is inserted as an integer number of seconds, and doesn’t work right when trying to use the julianday() function

bash-3.2$ sqlite3
SQLite version 3.29.0 2019-07-10 17:32:03
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table car_table(car_name text, car_date text);
sqlite> insert into car_table values ("Ford", date('now'));
sqlite> insert into car_table values ("Toyota", date('now','7 days'));
sqlite> insert into car_table values ("BMW", strftime('%s','now'));
sqlite> select * from car_table;
Ford|2019-12-15
Toyota|2019-12-22
BMW|1576431883       <-- uh oh, this is in seconds, not a datestring
sqlite> select car_name, julianday(car_date) from car_table;
Ford|2458832.5
Toyota|2458839.5
BMW|                 <-- uh oh, this isn't a julianday, as expected
sqlite>              ^D to exit