CODEWITHSUNDEEP
sql
Ahsan Khan
Ahsan Khan

Reputation: 57

Daylight time in SQL

I am new SQL Developer user and haven't worked a lot with changing dates. I have a query which is showing information 1 hour past the actual time. It is because one of the tables in which the data is pulled from has the date always in CST while other tables consider the system date which has CDT or CST depends on the time of year

When I join the tables, they start 1 hour past the actual time. To compensate for this, I have to use DELIVERY_TIME+1/24 instead of DELIVERY_TIME but the codes need to be changed twice a year.

Is there any solution for this?

Upvotes: 0

Views: 70

Answers (1)

tadman
tadman

Reputation: 211610

You'l want to store all your database values as UTC whenever possible as these are as universal as you're going to get. You can render these out as local times in your application layer as these settings are often highly specific to the individual viewing the data.

If you have a particular delivery time every day, like 6pm, and you want to know where that time falls in UTC you can construct the time and convert it to UTC. Most time-zones that observe some form of DST flop back and forth 1h in their local time, but do not in UTC.

Normally there are functions like MAKEDATE() or MAKETIME() available in your database that can construct arbitrary points in time. Use those to make a time in your preferred timezone, then switch to UTC to keep them "fixed".

Any time you have code that "must be changed twice a year" you will have code that doesn't get changed and then chaos will ensue. Don't do this. You're just setting yourself up for failure.

As a note, doing this all in SQL can be very messy since databases don't always have up to date timezone information unless they lean heavily on the operating system. Your operating system is generally better equipped, it should get patched if things changed like it did in 2005. Even that was a total mess since a mix of patched and unpatched systems were in constant conflict.

Upvotes: 1

Related Questions