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.
150
questions
1
vote
1
answer
52
views
Alter timestamptz column to timestamp, without re-writing the table
I'm using Postgres Debezium source connectors, which does not support columns with timezone. I want to convert a table column from type from timestamptz to timestamp.
Postgres table stores data in UTC ...
- 113
0
votes
1
answer
45
views
Postgres unexpected results with time zone conversions
I am doing some imports of data that includes time zone conversion. Data that I am importing is in America/New_York timezone and I have to import it as UTC timestamp.
I tried some examples I found on ...
- 3
0
votes
1
answer
33
views
MySQL timezone tables: mysql_tzinfo_to_sql docs are incorrect
A basic installation of MySQL creates, but does not populate, the several timezone-related tables in database "mysql".
To populate those tables, you might consult:
https://dev.mysql.com/doc/...
- 101
0
votes
0
answers
61
views
Relationship between /etc/localtime and Etc/UTC in PostgreSQL
I run our company's DBMS in containers, and when creating a container, the ansible deployment role mounts the host file /etc/localtime along the same path into the container (although, as far as I ...
- 1
1
vote
0
answers
106
views
InfluxDB not returning results when group by time offset at a summer/winter time change
Let's start with an example. I've got an application that logs a point every minute. When I aggregate this data into a query where I'll have the count of logs per day, I get the following result. Keep ...
- 121
0
votes
1
answer
21
views
Postgres: any traps when adding tables with `timestamptz` fields, when existing tables all use `timestamp`?
Similarly as in Convert Postgres TIMESTAMP to TIMESTAMPTZ, but with the difference that I don't plan to modify existing table's timestamp columns. Are there any gotchas when using timestamptz for new ...
- 101
0
votes
0
answers
41
views
Why a postgreSQL timestamp with time zone at a certain time zone is printed without the time zone
When I ask PostgreSQL for a timestamp with time zone at a certain time zone, it does not print the time zone:
SELECT now()::timestamp WITH TIME ZONE AT TIME ZONE 'CST';
timezone
--...
- 384
1
vote
0
answers
205
views
is it possible to set specific timezone for session in mariadb connector/j
I 'm trying to use timezone setting with mariadb connector/J version 3.x.
In the documentation,in Timezone consideration, it says:
'a timezone': connector will set connection variable to value. ...
- 11
0
votes
1
answer
2k
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
933
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
111
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,837
0
votes
1
answer
42
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
1k
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
1k
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
2k
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....
- 509
2
votes
1
answer
3k
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 ...
- 123
0
votes
2
answers
1k
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 ...
- 301
1
vote
1
answer
393
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
3k
views
ALTER timestamp column to timestamptz, 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
887
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 ...
- 306
1
vote
1
answer
114
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
3
votes
2
answers
3k
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:
...
- 137
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 ...
- 32.6k
2
votes
2
answers
2k
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
10k
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
110
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
4k
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(*...
- 259
2
votes
1
answer
6k
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 ...
- 265
1
vote
1
answer
291
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
931
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
1k
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
16k
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
4k
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
3
votes
2
answers
1k
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....
- 513
7
votes
2
answers
10k
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 ...
- 513
7
votes
1
answer
4k
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,602
1
vote
1
answer
192
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
3
votes
1
answer
1k
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 ...
- 131
0
votes
1
answer
65
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 => ...
- 597
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
698
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
71
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 ...
- 906
1
vote
0
answers
574
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
574
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 ...
- 103
2
votes
1
answer
14k
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
5k
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
120
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
192
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
6k
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
42
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 ...
- 751