SQL Azure – Convert UTC to your local time

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 what parameter you should have for your convert statement.

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

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