kaizenpro
asked on
Current Date/Time SQL Azure
I have just found out that my SQL Azure database time is different to the time on my application code, due to Azure system time being set in a different time zone to my own.
I have a stored procedure on SQL that regularly looks at a table and performs actions on records that are 5 minutes older than the current time - (there is a date time field set during the initial transaction.) The Azure time issue obviously throws this out.
I could manually add a few hours on to my code to change the stored procedure to account for the time difference but this seems messy. I am also in GMT zone which changes to BST during the summer. Any suggestions about how I should account for the difference in time zones??
Thanks
I have a stored procedure on SQL that regularly looks at a table and performs actions on records that are 5 minutes older than the current time - (there is a date time field set during the initial transaction.) The Azure time issue obviously throws this out.
I could manually add a few hours on to my code to change the stored procedure to account for the time difference but this seems messy. I am also in GMT zone which changes to BST during the summer. Any suggestions about how I should account for the difference in time zones??
Thanks
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I don't think DST should matter. Whenever you retrieve current time from either the server or the workstation, DST will normally have been included. Of course, at the nights where you shift from 3 to 2 o'clock it may cause some confusion.
That said, this is a common issue, so browsing may reveal some recommended methods that will fit your exact scenario.
/gustav
That said, this is a common issue, so browsing may reveal some recommended methods that will fit your exact scenario.
/gustav
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I just ran a test from one of our Azure SQL databases:
select getdate() from sometable
and it returned
2016-04-24 07:03:02.187
which equals the current UTC time
Still, it doesn't solve your issue with DST unless you filter all dates into normal or summer time.
Perhaps one should store both UTC time, local (or "source") time, and the timezone difference as - in queries - it will be very slow always to have to convert time either to or from UTC.
/gustav
select getdate() from sometable
and it returned
2016-04-24 07:03:02.187
which equals the current UTC time
Still, it doesn't solve your issue with DST unless you filter all dates into normal or summer time.
Perhaps one should store both UTC time, local (or "source") time, and the timezone difference as - in queries - it will be very slow always to have to convert time either to or from UTC.
/gustav
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Good point. I just tried GetUtcDate() on Azure SQL and it returns the same as GetDate(), so the do run using UTC.
/gustav
/gustav
Yes, Azure SQL Databases are always UTC, regardless of the data center. But it is always better to create code reusable for different SQL Server versions so I would rather use GETUTCDATE() in stored procedures and applications than GETDATE().
ASKER
Thanks for everyone's input. I ultimately decided to use SQL time as standard throughout my application instead of the application's time.
Yep - standardizing on one timezone throughout (generally UTC/GMT is best) is definitely the right approach.
Doug
Doug
ASKER
Whilst the other suggestions work in principle, creating a function which returns the server time and using that across my code seems like a simple solution.
ASKER
First I would need to identify the time zone of the posting entry, then the time zone of the Azure database.
Then run a function that works out the difference between the two time zones.
Is this possible?