Category: Dates and Numbers

Louis Davidson knows what time it is:

Recently a topic came up at work, and I remember seeing it on Reddit, so you know, that definitely is a sign that I had to write a blog on it.

For a lot of people, we typically standardize on a single time zone for our life, and in turn our data. Maybe it is the time zone of our headquarters, our house, and sometimes it is the standard time zone UTC. It is often suggested that every organization should use UTC, and that is a great suggestion for many things.

However…if you store all your times as UTC, this becomes a nightmare for some kinds of reporting.

Click through for a primer on the capabilities of the DATETIMEOFFSET data type and some of the functionality you can use with it. And Louis hits on one important note around daylight savings time changes: the date changes over the years. Another is that what’s in the Windows or Linux time zone database isn’t always historically accurate. For example, in 1974 and 1975, the US extended DST and began it in January and Feburary, respectively. But if you use Louis’s query, it claims DST started in April, which would have followed the 1966-1973 and 1976-2006 patterns but is historically inaccurate. You’ll find these sorts of things for a variety of countries because Daylight Savings Time adherence is kind of wild.

Hubert Lubaczewski solves a problem:

Since I can’t copy paste the text, I’ll try to write what I remember:

Given table sessions, with columns: user_id, login_time, and country_id, list all cases where single account logged to the system from more than one country within 2 hour time frame.

The idea behind is that it would be a tool to find hacked account, based on idea that you generally can’t change country within 2 hours. Which is somewhat true.

Solution in the blogpost suggested joining sessions table with itself, using some inequality condition. I think we can do better…

Click through for a solution that works for PostgreSQL but not SQL Server because the latter doesn’t offer date and time intervals on window function frames.

To do this in SQL Server, I’d probably use LAG() and get the prior value of country ID and the prior login time. Something like the following query, though I didn’t run detailed performance checks.

WITH records AS
(
	SELECT
		s.user_id,
		s.login_time,
		s.country_id,
		LAG(s.login_time) OVER (PARTITION BY s.user_id ORDER BY s.login_time) AS prior_login_time,
		LAG(s.country_id) OVER (PARTITION BY s.user_id ORDER BY s.login_time) AS prior_country_id
	FROM sessions s
)
SELECT *
FROM records r
WHERE
	r.prior_country_id <> r.country_id
	AND DATEDIFF(HOUR, r.prior_login_time, r.login_time) <= 2;