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.
142
questions
0
votes
1
answer
18
views
Postgres changing data type from timestamp to timestamptz (with time zone)
I recognize there are some similar questions, and I've read quite a few, so pardon me if I just missed the answer.
I have data that was stored as a TIMESTAMP data type, but now we want it to be stored ...
- 11
0
votes
0
answers
45
views
TimeZone Setting On PostgreSQL Databases
I have a Patroni cluster with two Ubuntu 22 db server. The timezone of my instances was set to UTC. I have needed to edit them to set the local timezone to Europe/Istanbul. I have made the timezone ...
user274715
0
votes
0
answers
46
views
MySQL System Timezone using Abbreviation (AST) vs Olson Timezone (America/Curacao)
I am working on a MySQL 5.7.41-44 instance running on a CentOS server.
The Linux timezone (per timedatectl) is America/Curacao (AST, -0400).
The MySQL @@system_time_zone returns AST (and not America/...
- 2,767
0
votes
1
answer
17
views
How to express dates for display in neighboring timezones?
I run a toy database where users can browse around, find toys they own, and "add them to their collection". On the back end, I add a row to a dedicated MYSQL table with data about the ...
- 1
0
votes
1
answer
476
views
How to get current date and time in Elasticsearch
I need to review the current date and time zone from Elasticsearch. I'm checking the elastic documentation and it mentioned that the default value is UTC.
In other environments I use:
SELECT NOW();
...
- 37
1
vote
1
answer
195
views
Find rows between two timestamps -- timezone conversion problems
I have a column called time_utc. This is of type timestamp, but it's important to note that the date is always January 1 2023, but at varying times.
I want to return the rows where time_utc is between ...
- 113
0
votes
1
answer
658
views
Does changing server time zone "shift" SQL Server stored date / time values in ODBC / JDBC?
I've read that SQL Server:
uses system time zone on Windows (reg key HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\TimeZoneInformation\TimeZoneKeyName)
uses TZ env var on Linux or /etc/timezone....
- 489
1
vote
1
answer
858
views
Is it acceptable to store global date times in UTC without offset for timezone?
I'm looking at a Data Warehouse which collections data from systems across the world. The Data Warehouse uses UTC for time but only stores the UTC offset and not the timezone. I have noticed that ...
- 113
0
votes
1
answer
540
views
Java PostgreSQL library seems to convert dates to local time zone automatically
PostgreSQL 14.6
Ubuntu 22.04
I am using postgresql-42.5.4.jar which I downloaded from pgJDBC. I use this library to get data from a database and display it on a website running locally. The web server ...
- 281
1
vote
1
answer
207
views
Time zone conversion in DMS from DB2 to Aurora Postgres
Challenge
We are using AWS DMS to convert data from IBM DB2 into AWS Auroa Postgres
IBM DB2 server is set to Eastern time (ET) zone. Timestamp related data inside DB2 is therefore in ET.
Findings
AWS ...
5
votes
1
answer
1k
views
ALTER timestamptz column to timestamp without "converting" data?
It seems that altering a column from timestamp without time zone to timestamp with time zone converts the existing data based on the current session time zone at the time of the alter statement.
See ...
2
votes
1
answer
332
views
How are time value comparisons between `timestamp` and `timestamptz` carried out in postgres?
Given the following data:
CREATE TABLE tbl (
x timestamp,
y timestamptz
)
INSERT INTO tbl(x, y)
VALUES
('2021-01-01'::timestamp, '2021-01-01'::timestamptz)
;
It's not obvious to me that ...
- 276
1
vote
1
answer
72
views
How to create a variable while using GROUP BY in MySQL
I have these functional queries where I need to change timestamp in UTC to timestamp with different time zones
SELECT max(user_id) AS user_id,
AVG((model_v3)*40) AS avg_score,
...
- 29
2
votes
1
answer
1k
views
Retrieving the local timezone inside SQLite
I have an SQLite table containing a "last changed" column with a date in the format e.g. "2022-11-07T11:51:06+01:00". Coreutils' date outputs this by using the following command:
...
- 127
10
votes
1
answer
1k
views
Can AT TIME ZONE return inaccurate results for data before 2004?
SQL Server 2016 added the AT TIME ZONE operator. From the documentation:
AT TIME ZONE implementation relies on a Windows mechanism to convert datetime values across time zones.
AT TIME ZONE calls ...
- 31.8k
2
votes
2
answers
1k
views
Postgres: why does to_timestamp return the date with different timezones, in the same query?
Why does to_timestamp use different timezones to display epochs, in the same query? Note +02 vs +01.
=> select to_timestamp(1651157248),to_timestamp(1647224832);
to_timestamp | ...
- 145
3
votes
1
answer
6k
views
How to query dates in different timezones?
I have a table and index in a PostgreSQL 10.18 database:
CREATE TABLE some_table (
expires_at timestamptz
);
CREATE INDEX ON some_table(expires_at);
Is there a way to write this query in a way to ...
- 185
1
vote
0
answers
93
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 ...
- 182
1
vote
1
answer
2k
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(*...
- 219
1
vote
1
answer
3k
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 ...
- 255
1
vote
1
answer
141
views
Postgres timezone issue when using postgres_fdw
I have a view stored on a foreign Postgres server (v 9.3) 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 (...
- 73
0
votes
0
answers
710
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(&...
- 11
1
vote
1
answer
910
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 ...
- 179
1
vote
1
answer
10k
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' ...
3
votes
1
answer
3k
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 ...
- 141
2
votes
2
answers
455
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....
- 503
7
votes
1
answer
7k
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 ...
- 503
7
votes
1
answer
3k
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 ...
- 6,612
1
vote
1
answer
132
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 ...
- 251
2
votes
1
answer
685
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 ...
- 121
0
votes
1
answer
54
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 => ...
- 547
0
votes
0
answers
1k
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 ...
- 101
0
votes
1
answer
418
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 ...
1
vote
0
answers
63
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 ...
- 890
1
vote
0
answers
440
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 ...
- 111
0
votes
1
answer
469
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 ...
- 3
2
votes
1
answer
10k
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 ...
- 21
3
votes
1
answer
3k
views
Any downside to using timestamp without time zone if UTC is ok at all times
timestamp with time zone vs timestamp without 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. ...
- 41
0
votes
1
answer
91
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 ...
- 111
0
votes
0
answers
162
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 ...
2
votes
2
answers
5k
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 ...
- 123
0
votes
1
answer
38
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 ...
- 731
1
vote
1
answer
2k
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 ...
- 13
5
votes
1
answer
972
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 ...
- 53
2
votes
1
answer
4k
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 ...
- 21
0
votes
1
answer
948
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 ...
- 173
0
votes
3
answers
3k
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 ...
- 149
0
votes
0
answers
438
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....
- 486
-3
votes
1
answer
2k
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
3
votes
1
answer
102
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?
...
- 131