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.
21 questions
18
votes
1
answer
46k
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 ...
- 65.9k
11
votes
1
answer
7k
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'::...
- 245
13
votes
3
answers
7k
views
"AT TIME ZONE" with zone name PostgreSQL bug?
I was answering this stackoverflow question and found strange result:
select * from pg_timezone_names where name = 'Europe/Berlin' ;
name | abbrev | utc_offset | is_dst
---------------+--...
- 678
5
votes
1
answer
2k
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 ...
- 297
6
votes
1
answer
4k
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 ...
39
votes
2
answers
71k
views
How to best store a timestamp in PostgreSQL?
I'm working on a PostgreSQL DB design and I am wondering how best to store timestamps.
Assumptions
Users in different timezones will use the database for all CRUD functions.
I have looked at 2 options:...
- 579
35
votes
11
answers
233k
views
How can I get the correct offset between UTC and local times for a date that is before or after DST?
I currently use the following to get a local datetime from a UTC datetime:
SET @offset = DateDiff(minute, GetUTCDate(), GetDate())
SET @localDateTime = DateAdd(minute, @offset, @utcDateTime)
My ...
- 8,567
35
votes
2
answers
45k
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 ...
- 453
8
votes
1
answer
3k
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 ...
- 664
27
votes
3
answers
63k
views
How do I resolve this error, "ERROR 1298 (HY000): Unknown or incorrect time zone: 'UTC'"?
When I run the following command I get an error, however one of my scripts requires it.
SET time_zone = 'UTC';
ERROR 1298 (HY000): Unknown or incorrect time zone: 'UTC'
- 65.9k
14
votes
2
answers
10k
views
Handling time zones in data mart/warehouse
We are starting to design the building blocks of a data mart/warehouse and we need to be able to support all time zones (our clients are from all over the world). From reading discussions online (and ...
10
votes
2
answers
5k
views
Data Warehouse design for reporting against data for many time zones
We are trying to optimize a data warehouse design that will support reporting against data for many time zones. For example, we might have a report for a month's worth of activity (millions of rows) ...
- 203
6
votes
1
answer
1k
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 ...
- 163
4
votes
1
answer
2k
views
Strange UTC offset time zone parsing in Postgres
Postgres exhibits some strange behaviour when parsing time zones, or I just don't understand how it works.
From the documentation:
SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ...
- 627
3
votes
1
answer
28k
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 ...
- 185