SQL Azure – Convert UTC to your local time – and an interesting observation

Not unsurprisingly SQL Azure GetDate() stores datetime as a UTC value.

If your application is hosted in a specific country it is likely you will wish to return that value to the local user in the local time.
Here I host in the UK and want to display in the UK so my choice is simple enough.

The default CONVERT() statement in the SQL Azure library will do this for you.

An example of the statement with parameters is here (slightly confusingly here I have the field name as a reserved word but just ensure that your field name is in the square brackets)

CONVERT(datetime, 
SWITCHOFFSET([datetime], 
DATEPART(TZOFFSET, 
[datetime] AT TIME ZONE 'GMT Standard Time'))) 
as UKTime

And here is that statement included in a full query.

And here is an SQL statement to determine all time zones currently observing daylight saving on your server.
This query can also be used to identify the pre-defined text string parameter you should have for your convert statement.

SELECT
  name,
  current_utc_offset
FROM sys.time_zone_info
WHERE is_currently_dst = 1;

Running this I get the following..

Now what is interesting about this is that the text string defined by Microsoft is GMT Standard Time and according to my investigations it definitely adjusts for British Summer Time (which most of the time is what I want!). OK but if you go to Wikipedia you get the following definition of what GMT is :-

Greenwich Mean Time or GMT is the time displayed by the Shepherd Gate Clock at the Royal Observatory in Greenwich, London. When the sun is at its highest point exactly above the Prime Meridian, it is 12:00 noon at Greenwich.

GMT is not affected by Daylight Saving Time (DST) clock changes.
The Greenwich Meridian (Prime Meridian or Longitude Zero degrees) is the reference point for every Time Zone in the world.

Every 15° longitude represents one hour’s difference in time: (24 x 15 = 360, the degrees of a circle). You can work out the time at every location on earth if you know how many degrees it is east or west of Greenwich.

GMT is not affected by Daylight Saving Time (DST) clock change -> So obviously that contradicts the definition in SQL Server – fair play to the engineer who set up the sys.table he just wasn’t aware. Probably won’t be changed now but its indicative of the danger there is in dealing with time when you need to be ultra accurate. Users tend to think that dealing with time is straight forward but in reality its not!

Multi-tenancy – Design Options

Some personal notes on different options for managing multi-tenancy

Silo – Different databases for each tenant. Again PHPRunner has an excellent solution for this.

Siloed Database with one Front End PHPRunner

Bridging – Unique schema for each tenant – for me this I can’t see any great advantages to this and quite a lot of extra required work.

Row Level Security – Tenancy managed by the database – One database additional column in tables that record the tenant and prior to showing results of query the database will filter results by tenant. Policy set up with unique user for each tenant. Postgres applicable. Still requires an additional field in all the tables but it is nice to have and can be extremely quick to implement.

Client Software – Client side implemented tenancy – PHP Runner has an option to implement Row Level Security in the design of the front end.

Amazon Web Services article on Multi-tenancy

Some points – for postgres implementing Row Level Security on tables does not carry through to the views which is a major issue for me. There are way rounds it though. It would be great to have the database managing the row level security and if you have all your tenants in one database possibly the best way to do it is using the below.

How to Add Row Level Security to Views in PostgreSQL