Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange
Make your voice heard. Take the 2019 Developer Survey now

Questions tagged [timezone]

The tag has no usage guidance.

-1
votes
0answers
24 views

Daylightsavings [duplicate]

DECLARE @locTime as time DECLARE @timee as nvarchar(10); set @locTime = (select Dateadd(minute, 330, getdate())) SET @timee = (SELECT LEFT(STUFF(CONVERT(NCHAR(8), @locTime, 8), 3, 1,''),...
1
vote
1answer
54 views

Mysql connection pool set timezone

Service application is nodejs It is using mysql connection pool. Currently, we will plan to sell service to another country What is the best way to set timezone? timezone setting execute, and query ...
1
vote
0answers
153 views

TimeZone problem with Oracle SQL Developer for Windows on MySQL server

I am trying to connect to a MySQL database using Oracle SQL Developer, but I get this error: Status: fail, test failed: Estado: Fallo:Fallo de la prueba: The server time zone value 'PDT' is ...
1
vote
0answers
18 views

SET TIME ZONE '-7' vs. AT TIME ZONE '-7'

This is similar to these questions: Why does PostgreSQL interpret numeric UTC offset as POSIX and not ISO-8601? Strange UTC offset time zone parsing in Postgres I'm running Postgres 10.3. As per the ...
1
vote
1answer
34 views

how to ignore DST in MySQL

We have a database with flights, and in there, departureTime DATETIME field. Extra info: global.time_zone: Europe/Dublin session.time_zone: Europe/Dublin MySQL version: 5.7 Problem is, I need to ...
3
votes
1answer
73 views

How to compare timestamps with different time zones?

For example I have a table with timestamps: 2018-04-05 06:00:00 +01 2018-04-05 06:00:00 +00 2018-04-05 06:00:00 -01 2018-04-05 04:00:00 -01 And I want to get entries that are ... ...
3
votes
1answer
163 views

Why does PostgreSQL interpret numeric UTC offset as POSIX and not ISO-8601?

Setting time zone as numeric offset (without abbrev, thus IMO not in POSIX format) is still interpreted by PostgreSQL as POSIX format: SET TIME ZONE '+02:00'; At least I assume, because checking the ...
3
votes
1answer
32 views

Why does postgresql differentiate between named and numeric time zone notations? [duplicate]

Why is there a difference between named timezone and numeric timezone offset, both of which yield different UTC timestamps for TIMESTAMPTZ? Assume Europe/Prague timezone, which is currently +02:00 (...
1
vote
1answer
188 views

timestamp with timezone issue with TZR vs TZH:TZM

I have a Hibernate with Oracle JDBC based application inserting/updating into the following column; COLUMN_A TIMESTAMP WITH TIME ZONE DEFAULT systimestamp NOT NULL but while it automatically appends ...
1
vote
1answer
60 views

Incorrect EST Timezone instead of EDT in Oracle

I am not getting correct timezone in Oracle Database. The issue is happening only starting 2021. Please see two same queries - first one for year 2020 and second for year 2021. The first one is ...
2
votes
2answers
87 views

AT TIME ZONE direction changes based on column having a timezone [duplicate]

Why does Postgres do the opposite thing for TS with and without Timezones? In one case it adds hours, in the other case it subtracts. Just to confuse things more, when specifying GMT+x, the hours ...
3
votes
2answers
100 views

Is there a way to avoid having chained/nested “replace()” items when using an ISO 8601 timezone offset value in a generated column?

I am using MySQL 5.7 and have a table that has the following columns that are being used to store some data that is source from an Apache common log formatted access log; details extracted from a ...
0
votes
1answer
178 views

Batch update timestamp records to correct timezone

I'm looking to batch update some timestamp records in postgresql. My app was storing timestamps to the timestamp column (without timezone) in the the wrong timezone (BST). I only noticed a while ...
2
votes
1answer
351 views

Update value in timestamptz column

I have a table column with the data type timestamptz. Time zone of server is America/Denver. Going through some strange issue while inserting a value in that column. When I update the column to the ...
-3
votes
2answers
285 views

How should query with timezone for timestamp

We saved the timestamp with UTC. However, we would like to choose user to their own time zone. There is no problem at display. But problem at the date range query. Asia/Singapore : 13/03/2017 0:00 ...
0
votes
1answer
286 views

Returning correct timezone offset from join

Meeting table |MeetingID | Start | Timezone | LocalStartTime |13 | 2018-01-31 19:00:00 | (UTC+10:00)Melbourne | |23 | 2018-01-31 19:00:00 | (UTC+10:00)...
1
vote
1answer
515 views

MySQL does not follow Ubuntu System Time Zone

An Ubuntu 16.04 server has it's time zone set to America/New_York using the command sudo timedatectl set-timezone America/New_York This is confirmed by running the commands timedatectl status | ...
1
vote
2answers
2k views

Convert a DATETIME value using string Timezone Info?

I have a column which has date, time, and timezone info like this: Sat, 20 Aug 2016 03:50:04 CDT Sun, 17 Sep 2017 16:51:46 CEST Mon, 09 Oct 2017 06:43:27 CET Thu, 28 Sep 2017 14:05:23 IST Thu, 05 Oct ...
9
votes
1answer
2k views

Daylight Saving time

In my environment, there are servers running on native backup and Ola Hallengren plans. Our servers are a combination of 2008, 2012 and 2014. All the full backups are taken at 12am and log backups are ...
0
votes
2answers
3k views

How to sync MySQL's time with system time without mysql restart?

On my machine I have a service to synchronise the SYSTEM time (including Time zone), and would like the DB will synchronize with the machine time once in a while. When I change SYSTEM time, MySQL ...
1
vote
2answers
340 views

Timezone offset displayed though it shouldn't be stored

I new to Postgres and v9.6.3 on a Linux system. From the docs and the PostgreSQL: Up and Running book, I learned that the timestamptz type just subtracts the offset of a timestamp on insertion and ...
5
votes
1answer
262 views

What is a valid use case for using TIME WITH TIME ZONE?

Along the lines of this related question: What is a valid use case for using TIMESTAMP WITHOUT TIME ZONE Are there any valid use cases for actually using TIME WITH TIME ZONE or should it be ...
0
votes
3answers
29 views

Managing timezones for user action datetimes

I hope this is the right place to ask the following question, and I make enough sense what is my concern. I have a table: mood_tracker, where I save about the feeling user has each day. CREATE ...
0
votes
0answers
691 views

How to Group By Date different time zone in SQL Server

We have date time stored in UTC. We want to make a report grouping by natural day in a especific zone. So we need to add in my query: GROUP BY CONVERT(DATE, DateTimeColumn) However, as this column ...
2
votes
1answer
6k views

How do I generate a date series in PostgreSQL?

If you're looking to generate a time series, see this question Let's say that I want to generate a series of dates between two dates. I see the function generate_series provides only Function ...
0
votes
2answers
2k views

Convert UTC time to local time with specific format in Redshift

I have a column called created_at with data stored in UTC without timezone like : 2017-02-05 15:43:27.151 I would like to convert to an other timezone like CET in specific format like : 2017-02-08 ...
1
vote
2answers
83 views

what does posix/Europe/Paris stand for?

If you execute something like the following query on Postgres: SELECT * FROM pg_timezone_names() WHERE name LIKE '%Rome%'; you get back a bunch of "weird" timezones: postgres=# SELECT * FROM ...
3
votes
1answer
142 views

Olson DB: which version in PostgreSQL?

I have an application heavily relying upon timezones, and I am using PostgreSQL for storing and querying my data. I'm letting the user choosing their own timezone, by showing the list of postgresql's ...
1
vote
1answer
899 views

Does AT TIME ZONE automatically take care of DST conversion?

I have to convert UTC dates for some reports. We are using SQL Server 2016 and all my report queries are SPs, so I was able to do this: SELECT MyDateTime AT TIME ZONE 'Pacific Standard Time' AS '...
2
votes
1answer
5k views

Why does MySQL calculate a wrong timestamp when using FROM_UNIXTIME

I have debugged this a lot and finally managed to reproduce it in the mysql shell. I am storing timestamps in a mysql database with the field type timestamp. I use FROM_UNIXTIME() to update them from ...
1
vote
1answer
67 views

PostgreSQL what happens if TimeZone is not set?

From the docs on 9.1, I see If timezone is not specified in postgresql.conf or as a server command-line option, the server attempts to use the value of the TZ environment variable as the default ...
4
votes
1answer
333 views

Oddities with AT TIME ZONE and UTC offsets

I don't understand the difference between these two columns. America/Chicago timezone is UTC-6, so I expect both to return the same result: select timezone('America/Chicago', '2017-01-01 12:00:00'::...
0
votes
1answer
292 views

MySQL - Turkey/Istanbul Daylight Saving Time Change

Until this year Turkey was using daylight saving time, staying UTC+2 on winter period, UTC+3 on summer. This year it is decided to stay only in UTC+3. Our DateTime's are persisted on MySQL (5.26.34) ...
1
vote
1answer
308 views

Windows PostgreSQL Update Timezone Data

Turkey government has announced that Turkey will stay on GMT+3. We've waited for OS pathces to update tzdata on PostgreSQL. Now, timezone patch for Windows is installed. On Linux, restarting ...
0
votes
2answers
2k views

Clock change on SQL Server

With the clock change ahead in Europe - how does SQL Server deal with the clock change? Are there known issues related to a clock change?
1
vote
0answers
98 views

Grouping time-series data by time intervals

Let's say we are storing data for 1000s of devices that collect a single type of data every 10s. Each device can be located in a different timezone. The ability to query quickly to visualize the data ...
4
votes
2answers
1k views

How to persistently set MySQL (Linux mysql command) session time zone to UTC?

I'm using MySQL on Linux (CentOS, recent release). I want the MySQL system variable time_zone to reflect UTC. When I start a MySQL session (using the command-line tool mysql) and check the time ...
18
votes
2answers
1k views

Why is AT TIME ZONE nondeterministic?

SQL Server 2016's AT TIME ZONE appears to be nondeterministic. However, I haven't been able to find documentation officially stating this or giving a rationale as to the reasoning behind it. Why is ...
0
votes
3answers
1k views

I can't find time zone tables in sys database of MySQL

For my Django application, I had to set the time zone to UTC, however I am not able to set it as I couldn't find any tables related to time zone in the system database. How do I get those? the ...
5
votes
1answer
9k views

How to get the timezone in SQL server [closed]

Was there any query to retrieve current timezone setting for the SQL server with respect to some offset like UTC..
0
votes
0answers
90 views

canada zipcode timezone

I'm not quite sure if this is the right place to ask this. If not I'm sorry in advance. I'm looking for a table which includes Canadian zip codes, cities, states, and time zones preferably in MySQL ...
5
votes
2answers
3k views

SQL Server 2014 accounts for Daylight Saving Time now?

My computer/SQL Server 2014 uses Eastern Time Zone which is 5 hours behind UTC time. But when I run the following code, I get a 4-hour difference. CREATE TABLE datetimeoffsetfunction( ...
2
votes
2answers
1k views

Installing time zone data on MariaDB 10.0

Yesterday, I set the time zone on our DB server (as explained here). Doing so required using the mysql_tzinfo_to_sql executable. Fortunately, this already existed on the server, and conversion of ...
1
vote
1answer
148 views

Querying dates with different timezones at db and application level

There is a created_at column of datetime type in the order table. In my application I'm storing dates to the db as datetime in UTC. When I bring them back from the db I'm presenting/formating them to ...
0
votes
2answers
4k views

How to set TIMESTAMP default value to 1970-01-01 00:00:01 UTC?

How can I set the default value for a TIMESTAMP column to be exactly 1970-01-01 00:00:01 UTC without changing time_zone? I tried the following: CREATE TABLE `foo` (`ts` TIMESTAMP DEFAULT CONVERT_TZ( ...
3
votes
1answer
363 views

PostgreSQL won't take new timezone

In Venezuela a timezone change have been undertaken on May 1st 2016. The old offset for the Venezuela timezone was -04:30 and the new one is -04:00. All database servers have been updated with the ...
1
vote
0answers
90 views

Time series data and utc conversion

I'm storing data in minute, hour, day, month and year buckets. All data is stored in UTC. Let's assume a client is in PDT time (-07:00 UTC) If the client wants to query for the hour sum of 4/23/2016 ...
1
vote
2answers
693 views

Why does Oracle change my date fields?

I have an Oracle 11g XE set up in Moscow, Russia, and therefore on MSK time zone. When a client in the same timezone stores a DATE variable from Java with, say time set to 12:00, the time is changed ...
4
votes
1answer
5k views

Convert Postgres TIMESTAMP to TIMESTAMPTZ

I have a decently-sized (~50k rows) time-series database running on Postgres, with some other structured data (in another database instance) which is much smaller. Stupidly, when I initially designed ...
0
votes
0answers
317 views

Oracle from_tz with Russian timezones shifts the input timestamp?

On Oracle 11g, I noticed that using from_tz to attach a TZ to a timestamp gives weird results for Russian timezones, but only after 25th October 2014. select from_tz(cast( '01-JUL-2015 12:00:00' as ...