Best practices for date/timestamps and SQLite queries?

My app has a table where each row has a time stamp. These rows contain data for a user’s actions and I want to be able to present the user with a lot of reports where the actions should be broken into times. Like comparing the average of X number of actions on Date 1 to the average of actions on Date 2.

To my novice mind this seems like a good place for making views on the fly. For example, if I want to present data for actions between 9-12 - create a view and manipulate using that.

But I thought to ask the forum what might be best practices?

Always use the format YYYY-MM-DD HH:MM:SS for dates. For collating date information, use the built-in sql date and time functions. Something like:

month(start_date) IN (9,10,11,12)

The actual function names differ between database engines though.

I would also strongly recommend either forcing an explicit gmt offset, or implying a static offset. If you don’t, you’ll run into problems as you change time zones, such as when traveling or daylight savings.

Never create a VIEW on the fly… they should be permenant fixtures in the database and as a convience for implementing complex queries from a central location (ie. the database itself).

Otherwise you “on the fly” is simply a QUERY… .not a VIEW

Personally I use seconds since the epoch (secondsSince1970) for all time stamps and then for date display, convert to whatever the user has requested.

[quote=412309:@Greg O’Lone]Always use the format YYYY-MM-DD HH:MM:SS for dates. For collating date information, use the built-in sql date and time functions. Something like:

month(start_date) IN (9,10,11,12)

The actual function names differ between database engines though.[/quote]

Seconded.

Standardizing a format and timezone internally (regardless of presentation) for dates and datetimes and using calendar tables for quick calculations, as well as creating a small set of data operation and manipulation methods to quickly add or substract (with options for working days and holidays) makes handling dates so much easier.

It also helps with localization later, when it inevitably arrives. :smiley:

For smaller units (common for comparisons, metrics, etc.) stick to ticks or microseconds.