none
How to set UTC timezone for specific database.

    Question

  • Hi,

    Is it possible to set the database time-zone with UTC for a specific database. 

    I did try to change the settings at OS level, but it reverted back to its old time-zone settings automatically in few minutes.

    Any possibility ?

    Friday, May 18, 2018 3:30 AM

All replies

  • Hello,

    A database is just a logical unit, it can not have something like a time zone at all. Time zone is a machine setting and when it reverts then because the machine is setup to sync time & settings with a NTP (Network Time Protocol) server.

    At all, what should that be good for changing time zone? Use UTC to store time values.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, May 18, 2018 3:49 AM
    Moderator
  • Thanks for the response.

    Pl. explain a bit more - do we need to use functions that keep the time-zone settings as the data landed into database ? or we should apply time-zone settings in Views by adding/subtracting hours ? or we can simply update the data using functions (the historical which is already in the system) and incremental data should be saved with required format.

    Suggest a best approach.

    Friday, May 18, 2018 4:45 AM
  • here is some information on  How to store UTC date and time in sql server and how to convert UTC date and time to local time.

    https://forums.asp.net/t/2072192.aspx?How+to+store+UTC+date+and+time+in+sql+server+and+how+to+convert+UTC+date+and+time+to+local+time+in+database

    Friday, May 18, 2018 5:03 AM
  • Not clear what your issue is or you trying to achieve, but we have several method & functions in SQL Server to handle time zones, see Date and Time Data Types and Functions (Transact-SQL)

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, May 18, 2018 5:42 AM
    Moderator
  • Let me explain the scenario:

    I want to insert data in my table - I want to add -5 hours in my datetime column.
    Can I achieve this under "Computer Column Specification".
    So every time data inserts in the table by adding -5 hours in it.

    Friday, May 18, 2018 6:03 AM
  • This is getting more and more confusing.

    If you want to store data in UTC, you use the function sysutcdatetime. If the time is coming from outside of SQL Server, you cannot simply apply a fixed offset, unless you know that the source you get the timestamps from will never be subject to daylight savings time.

    To answer your question about a computed column: a computed column in a table, is a column of which the value is determined by other columns in the table. Say for instance you are storing the point in time an order was placed with both date and time, but in many computations you only want the date or only the time. To make life simpler you can do:

       OrderDateTime datetime2(3)    NOT NULL,
       OrderDate AS cast(OrderDateTime AS date),
       OrderTime AS cast(OrderDateTime AS time(3))

    Specifically, you can index OrderTime if you have need to look for all orders that was placed on a certain time of day disregarding the date.

    So it does not seem like a computed column meet your needs. It sounds more like you would use a trigger to alter the incoming data, but I think this is better handled on the source side.

    Sunday, May 20, 2018 6:38 PM
  • Normally what you want to do is always store the data in UTC time in the database, and then translate UTC time to whatever local time zone you want when you need to do so, in the client code.

    Monday, May 21, 2018 6:20 PM
    Moderator