Skip to main content
Share Your Experience: Take the 2024 Developer Survey

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
1 answer
21 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 ...
markec's user avatar
  • 3
0 votes
1 answer
10 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/...
gwideman's user avatar
  • 101
0 votes
0 answers
46 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 ...
strafer's user avatar
1 vote
0 answers
86 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 ...
Ron Nabuurs's user avatar
0 votes
1 answer
14 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 ...
VanillaDonuts's user avatar
0 votes
0 answers
37 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 --...
s.k's user avatar
  • 372
1 vote
0 answers
148 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. ...
SkyKnight's user avatar
0 votes
1 answer
1k 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 ...
codki's user avatar
  • 11
0 votes
0 answers
324 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 ...
user avatar
0 votes
0 answers
87 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/...
GWR's user avatar
  • 2,807
0 votes
1 answer
32 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 ...
Max L's user avatar
  • 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(); ...
Carolina's user avatar
1 vote
1 answer
752 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 ...
Caspian's user avatar
  • 113
0 votes
1 answer
1k 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....
gavenkoa's user avatar
  • 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 ...
user3165854's user avatar
0 votes
1 answer
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 ...
Zephyrus's user avatar
  • 301
1 vote
1 answer
335 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 ...
Padraic Renaghan's user avatar
5 votes
1 answer
3k 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 ...
Padraic Renaghan's user avatar
2 votes
1 answer
756 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 ...
baxx's user avatar
  • 296
1 vote
1 answer
98 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, ...
Rollo's user avatar
  • 29
3 votes
1 answer
2k 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: ...
Tobias Leupold's user avatar
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 ...
Joe Obbish's user avatar
  • 32.2k
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 | ...
gernot's user avatar
  • 145
3 votes
1 answer
9k 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 ...
ffox003's user avatar
  • 185
1 vote
0 answers
105 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 ...
Jirka Hanika's user avatar
1 vote
1 answer
3k 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(*...
Ουιλιαμ Αρκευα's user avatar
2 votes
1 answer
5k 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 ...
Chris Halcrow's user avatar
1 vote
1 answer
221 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 (...
dcbeckman's user avatar
0 votes
0 answers
862 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(&...
Artem Repko's user avatar
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 ...
Joe's user avatar
  • 179
1 vote
1 answer
14k 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' ...
Prithvi Singh's user avatar
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 ...
Jer's user avatar
  • 141
2 votes
2 answers
998 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....
w0051977's user avatar
  • 503
7 votes
2 answers
9k 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 ...
w0051977's user avatar
  • 503
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 ...
Cade Roux's user avatar
  • 6,602
1 vote
1 answer
176 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 ...
Charles Burns's user avatar
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 ...
Rockerduck's user avatar
0 votes
1 answer
61 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 => ...
AndreKR's user avatar
  • 587
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 ...
Ivan Olshansky's user avatar
0 votes
1 answer
626 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 ...
Jatin Bisht's user avatar
1 vote
0 answers
70 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 ...
Metaphor's user avatar
  • 906
1 vote
0 answers
551 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 ...
Moritz Roessler's user avatar
0 votes
1 answer
531 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 ...
Ashu's user avatar
  • 103
2 votes
1 answer
12k 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 ...
ali ebrahimi's user avatar
3 votes
1 answer
4k 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. ...
Dajaku's user avatar
  • 41
0 votes
1 answer
114 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 ...
ToolmakerSteve's user avatar
0 votes
0 answers
180 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 ...
Prashin Jeevaganth's user avatar
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 ...
Lalit Fauzdar's user avatar
0 votes
1 answer
40 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 ...
exhuma's user avatar
  • 741
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 ...
jbush's user avatar
  • 13