Questions tagged [timezone]

A time zone is a region on Earth that has a uniform, legally mandated standard time. It is often represented by an identifier, such as "America/Los_Angeles". Note that a numeric modifier such as "-07:00" is an _offset_. A time zone encodes additional details like Daylight Saving Time.

Filter by
Sorted by
Tagged with
0 votes
0 answers
57 views

Can DST transitions cause "random" query timeouts?

I have encountered a query timeout ("Execution Timeout Expired") on a sub-second query on a connection set to 30 seconds timeouts. Client side logging indicated that 4 days elapsed between ...
user avatar
0 votes
1 answer
56 views

Time zone lost when returning timestamp with time zone from a function

I have this function: CREATE OR REPLACE FUNCTION laborable_day(dtDateTime TIMESTAMP WITH TIME ZONE) RETURNS TIMESTAMP WITH TIME ZONE AS $$ DECLARE _isHoliday BOOLEAN; BEGIN LOOP SELECT COUNT(*...
user avatar
0 votes
1 answer
73 views

Why do I see the timezone offset displayed in PgAdmin, for a timestamp with timezone

I'm using PgAdmin 4 v6, and for some timestamp data with data type of timestamp with time zone, I'm seeing it in PgAdmin like this: 2022-03-06 16:11:33+04 Why is the +04 being displayed? I've looked ...
user avatar
1 vote
0 answers
29 views

Postgres timezone issue when using postgres_fdw

I have a view stored on a foreign Postgres server (v 9.03) which pulls all of the data that was posted for the day. I have foreign tables (using postgres_fdw) stored on my local Postgres servers (...
user avatar
0 votes
0 answers
28 views

PostgreSQL COALESCE arguments change resulting timezone for DateTime

I have strange behaviour of COALESCE function of PostgreSQL. Depending on arguments of this function the result is different: SELECT "CreatedOn", -- 7/24/2020 8:15:03 AM COALESCE(&...
user avatar
1 vote
1 answer
131 views

Postgres initdb with custom timezone directory

How can I run Postgres initdb with a custom timezone folder? I've tried to use the -L flag and PGSHAREDIR env var to control where initdb searches for Postgres share data. However, those options don't ...
user avatar
  • 139
1 vote
1 answer
440 views

Time zone conversion in PostgreSQL without timezone does the opposite operation

Query: SELECT TO_TIMESTAMP('2021-10-24 20:00', 'YYYY-MM-DD hh24:mi') as d, (TO_TIMESTAMP('2021-10-24 20:00', 'YYYY-MM-DD hh24:mi') at time zone 'utc')::timestamptz at time zone 'ASIA/CALCUTTA' ...
user avatar
3 votes
1 answer
1k views

Why are there no "Daylight Time" time zones in SQL Server?

I am converting the times of some datetimeoffset columns from UTC to Eastern. I am doing this via the command UPDATE MyTable SET MyColumn = MyColumn AT TIME ZONE 'Eastern Standard Time' This seemed ...
user avatar
  • 141
2 votes
1 answer
68 views

E. Australia Standard Time appears to be one hour early?

I am trying to learn more about the sys.time_zone_info table. It all seems intuitive except for the E. Australia Standard Time entry found with this query: select current_utc_offset from sys....
user avatar
  • 493
6 votes
1 answer
1k views

Why is GetDate AND GETDATE() AT TIME ZONE 'GMT Standard Time' returning the wrong time?

Please see the code below: DECLARE @UKDateTime as DateTime SELECT @UKDateTime = GETDATE() AT TIME ZONE 'GMT Standard Time' print @UKDateTime PRINT GETDATE() This returns: Oct 4 2021 4:03PM Oct 4 ...
user avatar
  • 493
6 votes
1 answer
1k views

SQL Server 2012 equivalent of AT TIME ZONE capability

Is there a way in SQL Server 2012 to get what the server time zone would be on a particular date/time? In SQL Server 2016, you have AT TIME ZONE, but that's not available in 2012. I have a ...
user avatar
  • 6,582
1 vote
1 answer
54 views

Why is Oracle's TZ_OFFSET wrong for all cities on only one server?

Why does Oracle think that the timezone offset of Denver is UTC-7:00? The server time and systimestamp look right. Server 1: Oracle 11.2.0.4.0 SELECT SESSIONTIMEZONE FROM DUAL; America/Denver cat ...
user avatar
1 vote
1 answer
82 views

What is the reasoning behind PostgreSQL changing the timezone when changing a timestamp with time zone field?

I have a table with a "timestamp with time zone" column. For one row, it had this value: 2021-06-24 00:00:00+02. Then, I edited and saved it to be: 2021-06-24 11:00:00-05. Note that both the ...
user avatar
0 votes
1 answer
41 views

Date calculations give different results in different environments

This query: WITH RECURSIVE cte AS ( SELECT 0 n UNION ALL SELECT n + 1 FROM cte WHERE n < 20) SELECT n, ('2020-04-13 00:00:00 Europe/Berlin'::timestamptz - make_interval(days => ...
user avatar
  • 415
0 votes
0 answers
397 views

Compare datetimes with dfferent timezones in MySQL

I have a field `opened_at` in MySQL table that stores datetimes in string format with different timezones: 2021-01-30T18:07:40+02:00 2021-01-31T01:32:40+05:00 2021-01-31T21:17:40+03:00 etc. I need to ...
user avatar
0 votes
1 answer
71 views

Postgres DB Timezone Change

Can You please guide us on how we can change the time zone of the Database to UTC which is migrated from AWS which was in time zone UTC? Now the Postgres 12 is installed in Ubuntu 20 which shows (show ...
user avatar
1 vote
0 answers
58 views

Converting time zones

My SQL Server is located in US Mountain Standard Time where the Daylight Savings is not observed. My office is in US Central time zone. All schedules are kept in Central time by business. I am trying ...
user avatar
  • 820
0 votes
0 answers
51 views

Fix the timezone of timestamp in MySQL

From my misunderstanding, I stored time as timestamps and thought they were stored as UTC+0. But I moved into a different country and found the timestamps were stored in the local time. The issue is ...
user avatar
1 vote
0 answers
55 views

Correcting wrong timezones/timestamps in existing tables

I did a huge mistake because I didn't know how the TIMESTAMPTZ datatype works in postgresql. We need to store sensor data from a handful of different sources. We have a few tables with two timestamp ...
user avatar
0 votes
1 answer
161 views

Change default timezone to America Pacific

SELECT @@GLOBAL.time_zone, @@SESSION.time_zone; Result: SYSTEM SYSTEM SELECT CURRENT_TIME; Result: 06:25:07 But MY System time is 11.55 AM IST and PST TIME is 11.25 PM How can I change ...
user avatar
  • 3
2 votes
1 answer
3k views

Postgres epoch from current_timestamp

I've learned that epoch (UNIX timestamp) is a unique independent amount, so I am baffled by output I get from the following queries: select extract( epoch from current_timestamp::timestamp with time ...
user avatar
1 vote
1 answer
613 views

Timestamp with no time zone - if I'm ok with UTC at all times, is there any downside to using it?

Timestamp with time zone vs timestamp with no time zone. I'm aware that timestamptz is the same as timestamp no time-zone + (time-zone difference) that gets added upon saving a value in a database. ...
user avatar
  • 21
0 votes
1 answer
52 views

How extract cldr timezones from xml file to store in a DB table? [closed]

In our DB, I need each location to be associated with a "timezone". These timezones are in https://github.com/unicode-org/cldr/blob/release-36-1/common/supplemental/metaZones.xml I want to make a ...
user avatar
0 votes
0 answers
102 views

Syntax error around converting timestamps to time

I am writing a function that requires some comparison between the difference date and time parts of the timestamp data type. However it keeps running into syntax errors and I cannot find out what is ...
user avatar
2 votes
2 answers
2k views

Time stored using now()::timestamp function stores wrong value

I'm using AWS RDS for a PostgreSQL 11 database with PGAdmin. I store creation date of every row using now()::timestamp to store date and time without time zone. The problem is the time stored is never ...
user avatar
0 votes
1 answer
33 views

Can I refuse inserts/updates with timestamps without time-zone in postgresql?

I use exclusively timestamps with time-zone in PostgreSQL, but I just noticed something slightly worrying: If a client inserts/updates a record with a naïve time-stamp, PostgreSQL falls back to a ...
user avatar
  • 631
1 vote
1 answer
1k views

Update record with converted epoch timestamp from another column

I'm trying to convert epoch data in one column (timestamp_epoch) to a legitimate timestamp in another column (timestamp) for every record in a table. In reading the PostgreSQL Documentation it ...
user avatar
  • 13
3 votes
1 answer
539 views

Preserving timezones explicitly in postgres

I've been doing quite some research on different ways of 'seeing' time and how to properly map it in Postgres but I'm still not certain on what to actually use. Several articles recommend or rather ...
user avatar
  • 33
1 vote
1 answer
3k views

MySQL CURDATE() and CURRENT_TIMESTAMP return different value?

I am facing a little confusion because of CURRENT_DATE value and CURRENT_TIMESTAMP value shows different date on MySQL. Scenario : 1) User calls API from Lumen backend to insert data on a table. One ...
user avatar
0 votes
1 answer
677 views

Disable mysql_tzinfo_to_sql during MariaDB/MySQL installation

MariaDB run in docker container hangs during initial startup while mysql_tzinfo_to_sql running because of the issue. I've disabled mysql_tzinfo_to_sql by setting MYSQL_INITDB_SKIP_TZINFO env. var to ...
user avatar
  • 173
0 votes
3 answers
2k views

Oracle 12c and Jobs running one hour apart after changing time for hourly use

I have an Oracle 12c database in which I have detected that the scheduled jobs are running one hour apart after the change of time on the server, checking in the database I see that the SYSTIMESTAMP ...
user avatar
0 votes
0 answers
282 views

MySQL event scheduler -- Event running at wrong time

I have created a event to run EVERY 1 DAY at 3:15 (am). I know that timezones affects events and actually I create a query to check the START time in mysql.event (GMT timezone) and information_schema....
user avatar
-3 votes
1 answer
986 views

How to convert UTC time to New York time zone in Microsoft SQl [closed]

How to convert UTC time to New York time zone in Microsoft SQL
user avatar
3 votes
1 answer
81 views

What happen with the Log backup if I set the SQL Server clock back?

What happen with the Log backups if I set the SQL Server clock back? In countries with daylight saving this happens at least once a year. Will the point in time restore with the STOPAT option fail? ...
user avatar
  • 31
0 votes
2 answers
148 views

MariadB Europe/Athens Timezone

I am using Debian 8, mariadb 10.4, nginx 10.3, php7.3-fpm. My timezone configuration was like this: Debian: Europe/Athens ( through tzselect command ) PHP: Europe/Athens , Default timezone UTC ...
user avatar
1 vote
2 answers
2k views

How do I know if my PostgreSQL is up do date with DST rules?

Recently (2019-04-25) brazilian government ended the use of daylight savings time all around the country. In my current project I rely on PostgreSQL to do timezone conversions and analytics based on ...
user avatar
0 votes
1 answer
522 views

Two databases same server with different time zones

I want to have two databases on same server but want the data in each to reflect the time zone of the client. Do I need to adjust database parameters or can this be handled with client (JDBC or OCI) ...
user avatar
  • 1
0 votes
0 answers
275 views

In mysql system_time_zone doesn't reflects DST changes, isn't it a bug?

If a Ubuntu machine was in 'PST' timezone and on 10 march 2019 at 2:00 AM its timezone gets changed to 'PDT' timezone to accommodate DST changes then does this change is immediately reflected in ...
user avatar
  • 101
1 vote
1 answer
592 views

Issue with Time Zone while migrating MySQL on-premises DB to AWS RDS

I am having MySQL 5.6 database with PST time format in on-premises server. Now we are willing to migrate the database to RDS. For this, we have taken backup from physical server and restored in RDS ...
user avatar
  • 977
1 vote
1 answer
1k 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 ...
user avatar
  • 144
3 votes
2 answers
5k 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 ...
user avatar
1 vote
0 answers
26 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 ...
user avatar
  • 11
0 votes
1 answer
515 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 ...
user avatar
  • 9
4 votes
1 answer
2k 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 ... ...
user avatar
4 votes
1 answer
1k 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 ...
user avatar
  • 257
3 votes
1 answer
99 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 (...
user avatar
  • 257
1 vote
1 answer
3k 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 ...
user avatar
1 vote
1 answer
613 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 ...
user avatar
  • 13
2 votes
2 answers
267 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 ...
user avatar
  • 573
3 votes
1 answer
395 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 ...
user avatar