Suppose you’re working on a web application to support users in multiple time zones. How do you handle dates and times? If your users all live in one time zone, it’s easy. Simply store important date and time data in DATETIME or TIMESTAMP columns, and be done with it. But, what application has users in just one time zone? Maybe a municipal… Read more →
Category: SQL Time Processing
Using SQL to report by time intervals in Oracle
This is an Oracle-specific edition of the article explaining the same subject for MySQL. It’s often helpful to use SQL to group and summarize information by periods of time. For example, we might like to examine sales data. For example, we might have a table of individual sales transactions like so. Sales: sales_id int sales_time date net decimal(7,2) tax decimal(7,2) gross decimal(7,2)… Read more →
Specialized Time-Series Storage and Retrieval
There’s a relatively new software system for storing, retrieving, and processing time series. InfluxDB Read more →
Filling in missing data with sequences of cardinal integers
When you’re extracting summaries of information from a database, sometimes rows are missing. For example, if you are keeping track of number of customers by day, you might use a query like this: https://gist.github.com/OllieJones/530a243c8207fc02238e#file-1 You might get a result set like this: 2014-03-07 122 2014-03-08 355 2014-03-10 234 2014-03-11 119 Notice that the row for 2014-03-09 is missing from this… Read more →
SQL Reporting by time intervals
A version of this article specific to the Oracle DBMS is here. It’s often helpful to use SQL to group information by periods of time. For example, we might like to examine sales data. For example, we might have a table of individual sales transactions like so. Sales: sales_id int sales_time datetime net decimal(7,2) tax decimal(7,2) gross decimal(7,2) Each time… Read more →
What’s a date?
What is a date? This seems like a silly question. Indeed, if you are an independent local business person, it is a silly question. A date is, for example, the seventh of September, 2011 (“2011-09-07”). It describes a period of 24 hours that starts at midnight and ends just before midnight. If you only care about dates in, let’s say,… Read more →
Processing dates and times in SQL
I’ve spent too many working days figuring out how to handle dates and times in relational data base management systems. From looking at the questions on stackoverflow.com it’s obvious that a lot of people have the same kinds of questions and confusions I have. I’m a lazy programmer. I’d much rather do this stuff right than do it over, and I… Read more →