July 16, 2012 at 9:38 pm
Comments posted to this topic are about the item Time Zones
July 17, 2012 at 1:15 am
In two minds about this myself. Yes, it would certainly be handy to have these conversion functions in SQL server, but on the other hand, the old mantra of "do the formatting in the front-end application, not the database" surely applies?
July 17, 2012 at 1:57 am
Dates are the most cumbersome thing in SQL Server IMO. I always disliked having to write those convert statements to ditch a time component and then compare it to something a developer decided to dump in a varchar field. I'm currently using MySQL for a datawarehouse and whilst I miss almost everything about SQL Server, the date type and functions in MySQL are by far much easier to deal with.
I'd love to see a complete overhaul of dates in SQL Server, but I guess it's like why features don't get completely re-factored on old systems - it'd stop all the older solutions from working with it so you have to rewrite all your older code.
July 17, 2012 at 2:24 am
You might want to consider that there are 5 different definitions of 'CST' (http://www.timeanddate.com/library/abbreviations/timezones/). But even if we did have codes that are well known and unique, I think that what you want might not be that straightforward.
In theory I suppose SQL Server could know exactly about the past definitions (or at least the relative time differences) of all timezones (including e.g. whether or not here was daylight savings for your flavour of 'CST' in, say, 1861).
But what about events in the future? Do you want the outcome of your query to change potentially with an update to the timezone definitions? Daylight savings is only one reason why this might (or is likely to?) happen.
Or would you be happy if this was only reliable for past and/or nearby dates?
July 17, 2012 at 3:00 am
If you think Time Zones are a bother, just think about the similar - but different - issue of Daylight Saving Time (DST). An offset from the Offset as it were.
** EDIT ** Just noticed Alex has jumped on this too ....
AFAIK, SQL Server has no concept of Time Zone Offset (a property of a datetime Object?), so there's no innate potential for handling Time Zones per se.
I guess this would be because few organisations think globally - like the Military notion of "zulu time" or the optimistically named UTC - "universal" - time (anyone actually asked those living elsewhere in the Universe?)
With DST, there is always going to be that hour (in the cases I've come across) on the last Sunday in October where you just can't derive the DST zone from a (even Locale-assumed) datetime value at all ...
I got as far as generating an in-line expression (ie NOT a multi-statement procedure/function) for the UK that determines whether a datetime is GMT, BST or "unknown" - I figure that's as good as I'm gonna get ...
There ... I feel MUCH better now I've got that off my chest ...
Apologies if I'm merely displaying my ignorance ...
Russ
July 17, 2012 at 4:10 am
You are of course right that working with dates in T-SQL could be much easier, but the example you propose cannot work correctly, in any system, ever:
DECLARE @dt datetime
SELECT @dt = GETDATE()
SELECT CONVERT( DATETIMEOFFSET, @dt, 'CST')
By using GetDate(), which returns a local time without timezone information, you're already doomed to fail at accurately converting between time zones: there is a two-hour window during Daylight Savings Time switchover, every year, where GetDate() will return the same exact value in either hour, for any servers configured to implement daylight savings time. During that one hour every year, there is absolutely no way to get back to the "real" GMT date/time from a local timezone-unaware time.
Now if you used UTC, it's almost that simple already.... The main issue we have today is a lack of a SQL Server-native time zone table or conversion function, telling us what the correct offest is at any given point in time for a given time zone.
DECLARE @dt datetimeoffset = GETUTCDATE()
SELECT SWITCHOFFSET(@dt, '-05:00')
The most painful thing about the current offering is just that SQL server right now has no helpers for daylight savings, you must explicitly specify the offset from GMT, having determined it at some other level of your application.
http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.
July 17, 2012 at 5:39 am
Steve Jones - SSC Editor (7/16/2012)
Sometimes I wonder if we are looking too much into things we can add to an application rather than the things that we can improve.
Software companies are not in the business of making usable products. They are in the business of making marketable products - things that get bought. It's easier to sell some new doodad than say that last's year's doodad is improved. This is especially true of Microsoft. They change their operating systems to get people to buy them. They don't focus on making them better.
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn
July 17, 2012 at 5:44 am
Thomas Abraham (7/17/2012)It's easier to sell some new doodad than say that last's year's doodad is improved.
Surely adding the timezone feature mentioned by Steve would count as a "new doodad" as far as SQL Server is concerned?
July 17, 2012 at 5:54 am
paul.knibbs (7/17/2012)
Thomas Abraham (7/17/2012)It's easier to sell some new doodad than say that last's year's doodad is improved.
Surely adding the timezone feature mentioned by Steve would count as a "new doodad" as far as SQL Server is concerned?
It might. Depends on your perspective. And maybe that's the other side of the equation. Except in small shops, do the developers get to pick the DBMS? Or is it made at a management level where a time zone doodad (starting to wish I'd used a different word) is less important than failover/replication (for example)?
If I were marketing the product, I'd add in the features wanted by the people that make the purchase decision, not necessarily the people that get down and dirty with it.
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn
July 17, 2012 at 6:34 am
DECLARE @dt datetime
SELECT @dt = GETDATE()
SELECT CONVERT( DATETIMEOFFSET, @dt, 'CST')
and Iād get my current time in Central Standard Time (CST).
I don't know where you live, Steve, but here in the US Midwest it's current CDT:-)
So if I were to run this script on a server in New York (currently EDT), should it raise an error because no such conversion is possible, do the math mindlessly (return EDT) or just return NULL? It seems to me the application needs intimate knowledge of past and present clock change rules in order to specify the correct time zone. Although that mitigates the utility such functions, I agree of time-zone aware functions would still be useful.
July 17, 2012 at 6:34 am
Personally, I believe that formatting and local adjustments should be performed by the application/UI layer and not the database. Dates in the database should be stored as UTC and fetched as UTC. If the application wants it in the Eastern time or in the Western time, the application should convert the UTC accordingly.
The reasoning in my mind is that the data is constant - an event happened at a given moment. That moment itself does not change, only it's representation changes. Representation is done by the UI and hence the UI layer should be the one converting and formatting the date as required.
The above is just my opinion. It's really an interesting thread and topic to follow and hence I eagerly look forward to the experiences and opinion of esteemed community members.
Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Follow me on
Twitter: @sqltwins
July 17, 2012 at 6:43 am
Nakul Vachhrajani (7/17/2012)
Personally, I believe that formatting and local adjustments should be performed by the application/UI layer and not the database. Dates in the database should be stored as UTC and fetched as UTC. If the application wants it in the Eastern time or in the Western time, the application should convert the UTC accordingly.The reasoning in my mind is that the data is constant - an event happened at a given moment. That moment itself does not change, only it's representation changes. Representation is done by the UI and hence the UI layer should be the one converting and formatting the date as required.
The above is just my opinion. It's really an interesting thread and topic to follow and hence I eagerly look forward to the experiences and opinion of esteemed community members.
This reminds me of a discussion I had with my wife over the weekend, inspired by the line from an old "Chicago" song: "Does anybody really know what time it is?" Skipping the interesting bits of that conversation, I think Nakul is correct. Dates/times should be stored in a universal format, with no interpretation. Then the application can determine the best format in which to display the data to the user. Of course, that necessitates tiresome conversions for every date value every time. (Not every time used, just every time presented to the user. You can still do queries and calculations in the universal format.) But, it is the "right" thing to do.
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn
July 17, 2012 at 6:44 am
AARRGH! I hate DST.
There is just no easy way to manage schedules that fire in UTC so that they are aware of the switch. I ended up writing a script that I run twice a year to manage the change. Of course, this isn't even correct as our company has more clients beyond North America.
July 17, 2012 at 7:06 am
I'm with Steve on this one. Handling dates, timezones & formats is simply harder than it needs to be. As someone who's not in a true DBA role, but nonetheless has to write a lot of queries, SSRS reports, etc., it should just be easier -- period.
July 17, 2012 at 7:28 am
Two points are key from my perspective; timezone and magic values.
Timezone should be UTC. A perfect example of why this should be was in a paper/article/book I read. They suggested trying to merge databases using different timezones. It would be difficult to achieve. This scenario could occur due to centralisation of departmental databases in that were physically located in different timezones or through M&A (Mergers & Acquisitions).
Yet again formats are being suggested as being developer coded strings - probably just to get someone's back up š What's wrong with offering some standard formats as constants?
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
Viewing 15 posts - 1 through 15 (of 110 total)
You must be logged in to reply to this topic. Login to reply