Tag Info

Hot answers tagged

10

To go along with @ypercube's comment that CURRENT_TIMESTAMP is stored as UTC but retrieved as the current timezone, you can affect your server's timezone setting with the --default_time_zone option for retrieval. This allows your retrieval to always be in UTC. By default, the option is 'SYSTEM' which is how your system time zone is set (which may or may ...


5

Always store data in the database in UTC and then use the appropriate timezone to convert it during display, in the application. Is the only sensible approach when it comes to consider data mobility (geo-replication), user location and , most importantly, daylight savings. If you store local time there will be times when you cannot distinguish between column ...


3

The best way to convert a non-current UTC date into local time is to use the CLR. The code itself is easy; the difficult part is usually convincing people that the CLR isn't pure evil or scary... For one of the many examples, check out Harsh Chawla's blog post on the topic. Unfortunately, there is nothing built-in that can handle this type of conversion, ...


2

I found this answer on StackOverflow that provides a User Defined Function that appears to accurately translate the datetimes The only thing you need to modify is the @offset variable at the top to set it to the Timezone offset of the SQL server running this function. In my case, our SQL server uses EST, which is GMT - 5 It's not perfect and probably won't ...


2

You can use current_setting() function to get the TIMEZONE setting. SELECT current_setting('TIMEZONE') TZ; Sample output: # SELECT current_setting('TIMEZONE') TZ; tz ------------ US/Eastern (1 row) Here is SQLFiddle demo


1

To see all relevant settings for local time (and some irrelevant, too), you can use: SELECT * FROM pg_settings WHERE name ~* 'time' In particular, the settings for TimeZone and lc_time should be of interest to you. (The time zone is not the only relevant detail for time format.) You get a short description in the column short_desc.) More details for these ...


1

There are a couple of good answers to a similar question asked on Stack Overflow. I wound up using a T-SQL approach from the second answer by Bob Albright to clean up a mess caused by a data conversion consultant. It worked for almost all of our data, but then I later realized that his algorithm only works for dates as far back as April 5, 1987, and we had ...


1

You can not specify UTC_TIMESTAMP as default to specify automatic properties, You should use only the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses. Also you can INSERT UTC_TIMESTAMP values like this though for a table: CREATE TABLE `test` ( `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); INSERT ...



Only top voted, non community-wiki answers of a minimum length are eligible