July 17, 2012 at 11:37 am
And then you get the neighboring anomaly of Hawaii and Christmas Island. They are North/South of each other and their clocks read the same exact time, but on the calendar they're a day off from one another
July 17, 2012 at 11:44 am
Nobody has mentioned that to install SQL we had to install the .Net Framework and since 2005 we have had CLRs. We have several CLR/SQL functions here that make use of the TimeZone class
It can tell you your server's offset and whether or not it is daylight savings time. It can even do it for dates in the future and the past.
Here is some simple VB for determining if it is currently DST:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlBoolean IsDaylightSavingsTime()
{
return new SqlBoolean(TimeZone.CurrentTimeZone.IsDaylightSavingTime(DateTime.Now));
}
};
That is essentially one line of code and not so difficult. Search the site here for how to create a basic CLR based function for more details. After reading that you are done with the first hurdle. The formatting and remaining date functions can also be done this way. Once done, document and forget. Or at my company, just skip the documentation part. 😛
July 17, 2012 at 1:50 pm
Scott D. Jacobson (7/17/2012)
kevin77 (7/17/2012)
Completely get rid of time zones and Daylight Saving Time across the entire world!!!!So no matter where you are in the world, right now it is 16:21, period.
The state of Arizona in the US does not observe DST. I'm not really sure why but I have a feeling it's somehow related to their hatred of the federal government and their non-observance of Dr. Martin Luther King Day as well.
I think you are confused by listening to the media. 🙂
Arizona is just asking the Federal government to do their job, which the feds refuse to do. Further, there is no relation to DST and the current administration that is causing all the issues there, since they have never observed DST to my knowledge. It is unfortunate that so many people look to government to fix things, when they can't do anything more than break things, and even worse that they want the feds to usurp state's rights by passing laws and forcing the states to pay for them. As an example, what authority did Bush have mandating any change to DST - he didn't.
Dave
July 17, 2012 at 2:12 pm
djackson 22568 (7/17/2012)
Scott D. Jacobson (7/17/2012)
kevin77 (7/17/2012)
Completely get rid of time zones and Daylight Saving Time across the entire world!!!!So no matter where you are in the world, right now it is 16:21, period.
The state of Arizona in the US does not observe DST. I'm not really sure why but I have a feeling it's somehow related to their hatred of the federal government and their non-observance of Dr. Martin Luther King Day as well.
I think you are confused by listening to the media. 🙂
Arizona is just asking the Federal government to do their job, which the feds refuse to do. Further, there is no relation to DST and the current administration that is causing all the issues there, since they have never observed DST to my knowledge. It is unfortunate that so many people look to government to fix things, when they can't do anything more than break things, and even worse that they want the feds to usurp state's rights by passing laws and forcing the states to pay for them. As an example, what authority did Bush have mandating any change to DST - he didn't.
Oh zing. Looks like I struck a nerve. Seriously, no offense meant to our friends in AZ. 😀
July 17, 2012 at 2:33 pm
Tao Klerks (7/17/2012)
By using GetDate(), which returns a local time without timezone information, you're already doomed to fail at accurately converting between time zones:
The comment above (a snippet of the original) is incorrect. GETDATE() returns the server time. That might not be local time. At my first job, all the servers were GMT not Maryland, USA time. At my current job, we have some servers in St. Louis(Central) that are set to mountain time. And to add to it, Arizona doesn't do Daylight Savings Time. How do you code for all of that, since you first have to figure out if the server time is local time or not?
-SQLBill
July 17, 2012 at 2:45 pm
SQLBill (7/17/2012)
Tao Klerks (7/17/2012)
By using GetDate(), which returns a local time without timezone information, you're already doomed to fail at accurately converting between time zones:The comment above (a snippet of the original) is incorrect. GETDATE() returns the server time. That might not be local time. At my first job, all the servers were GMT not Maryland, USA time. At my current job, we have some servers in St. Louis(Central) that are set to mountain time. And to add to it, Arizona doesn't do Daylight Savings Time. How do you code for all of that, since you first have to figure out if the server time is local time or not?
-SQLBill
Bill, you jump out to the .Net Framework and get the answer there. See my post above about how to program the CLR.
July 17, 2012 at 2:52 pm
pmcpherson (7/17/2012)
SQLBill (7/17/2012)
Tao Klerks (7/17/2012)
By using GetDate(), which returns a local time without timezone information, you're already doomed to fail at accurately converting between time zones:The comment above (a snippet of the original) is incorrect. GETDATE() returns the server time. That might not be local time. At my first job, all the servers were GMT not Maryland, USA time. At my current job, we have some servers in St. Louis(Central) that are set to mountain time. And to add to it, Arizona doesn't do Daylight Savings Time. How do you code for all of that, since you first have to figure out if the server time is local time or not?
-SQLBill
Bill, you jump out to the .Net Framework and get the answer there. See my post above about how to program the CLR.
Looks like you can do it in Powershell as well. A script can be run on a schedule and the results stored it in a table for your convenient use.
July 17, 2012 at 3:12 pm
SQLBill (7/17/2012)
Tao Klerks (7/17/2012)
By using GetDate(), which returns a local time without timezone information, you're already doomed to fail at accurately converting between time zones:GETDATE() returns the server time. That might not be local time.
Haha, my bad, I meant "time local to the server", as opposed to "local geographical/political time, which the server may or may not be configured to follow" 🙂
You're right - if your servers happen to be configured in such a way that their local time offset to GMT is constant and known, then you can safely use GetDate() and at a later time convert to GMT - but why would you make that assumption when you can just use GetUtcDate() and have it work predictably and accurately on any server?
If you want to know your server's current "server local time" offset to GMT, that's the one timezone-related thing that's actually easy in 2008 and later: SELECT DatePart(tz, SysDateTimeOffset())
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 10:19 pm
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.
Before this gets too far out of control, I have to say I agree with Nakul. Have debated this over the years with devs. Time should be kept in UTC. Convert it elsewhere. It's not business logic, it's system logic. Have your logging or application figure it out outside of SQL Server.
July 18, 2012 at 1:53 am
kevin77 (7/17/2012)
Completely get rid of time zones and Daylight Saving Time across the entire world!!!!
Oh OK then.
All the best to all,
Gaz - from rainy England, home of GMT 😉
PS Personally I would give up DST at a drop of a hat too.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
July 18, 2012 at 5:07 am
Mad Myche (7/17/2012)
a perfect instance would be the State of Michigan, which has 75000 people an hour behind the rest of the state
What? What?? How did you get to be the most powerful nation in the world when you have two time zones in one state?
July 18, 2012 at 6:56 am
marlon.seton (7/18/2012)
Mad Myche (7/17/2012)
a perfect instance would be the State of Michigan, which has 75000 people an hour behind the rest of the stateWhat? What?? How did you get to be the most powerful nation in the world when you have two time zones in one state?
More timezones make you more powerful!
And no, you shouldn't count those big sun-based time zones like they have in Russia.
I'm talk'n small non-solar-based arbitrary zones where a sub-government takes 75,000 people and says, "You're not good enough; go back one hour!" and others that make DST optional.
It's little things like that that make us crazy psycho stronger! Yeah, stronger -- that's the word.
(Yes... humor intended)
-Chris C.
July 18, 2012 at 7:25 am
Chris.C-977504 (7/18/2012)
It's little things like that that make uscrazypsychostronger! Yeah, stronger -- that's the word.(Yes... humor intended)
-Chris C.
Been listening to some Negativland Chris? Time Zones
July 18, 2012 at 8:01 am
Scott D. Jacobson (7/18/2012)
Chris.C-977504 (7/18/2012)
It's little things like that that make uscrazypsychostronger! Yeah, stronger -- that's the word.(Yes... humor intended)
-Chris C.
Been listening to some Negativland Chris? Time Zones
Actually, no. That's the first I ever recall hearing about it.
I watched the video and then looked at wikipedia; the metion of "Car Bomb" stickers does ring a bell - I knew a guy that had that bumper sticker on a car in the late 80's. I just assumed it was either a 'solidarity reference' to Northern Ireland or it meant the car was a piece of crap (which it was).
FWIW, the humor origin for me was an old Saturday Night Live character that would say something like "Strongerrr, yeeeeeah... stronger. That's the ticket!"
I don't recall the name or actor, it just amused me when he would search for a word out loud and then reassure everyone that it was the right one by finishing with, "Yeah. That's the ticket!"
HTH,
-Chris C.
July 18, 2012 at 9:41 am
Well, I guess that it is all relative to what you are use to and how complicated you want it to be. A rose is still a rose it true to the florist, but a datetime is not a datetime to data pros any longer.
One database may store it clearly as some numeric value and another may use another data domain. And there are those who work datetime to be not the actual datetime but a time and the offset from Coordinated Universal Time or UCT. This time representation is the current local time plus or minus the offset. UTC BTW is the official time scale, maintained by the Bureau International des Poids et Measures(BIPM), and the International Earth Rotation Service (IERS).
And if you really want to be correct, there is a factor called the Leap Second that will better adjust the time to be more in line with the actual rotation of the earth.
This type of detail and confusion actually caused a rock group called Chicago Transit Authority, later just called Chicago, to ask the question, "Does anyone really know what time it is ?" 🙂
Not all gray hairs are Dinosaurs!
Viewing 15 posts - 31 through 45 (of 110 total)
You must be logged in to reply to this topic. Login to reply