26
votes
Accepted
How do I generate a date series in PostgreSQL?
You can use generate_series for this, but be sure to explicitly cast the arguments to "timestamp without time zone" otherwise they will default to "timestamp with timezone". PostgreSQL overloads ...
- 64.7k
15
votes
Accepted
Oddities with AT TIME ZONE and UTC offsets
A time zone name carries more information than an abbreviation or a simple time zone offset. 'UTC-6' is a "POSIX-style time zone specification" which is just an abbreviation plus offset.
The ...
- 182k
14
votes
How can I get the correct offset between UTC and local times for a date that is before or after DST?
For SQL Server 2016+, you can use AT TIME ZONE. It will automatically handle the day light saving times.
- 4,318
14
votes
Accepted
Can AT TIME ZONE return inaccurate results for data before 2004?
I don't know if this is definitive, but I found this Q&A on Stack Overflow that includes an answer from a former Microsoft employee (emphasis mine):
Does .NET have the history of time zone changes?...
- 29.8k
12
votes
Accepted
Why is GetDate AND GETDATE() AT TIME ZONE 'GMT Standard Time' returning the wrong time?
The documentation for AT TIME ZONE says:
Converts an inputdate to the corresponding datetimeoffset value in the target time zone. When inputdate is provided without offset information, the function ...
- 90.3k
11
votes
Accepted
Daylight Saving time
SQL Server expert Paul Randal addresses this very topic in How does daylight savings time affect disaster recovery?. It's a relatively short post, so I'm quoting it in its entirety. Since you were ...
11
votes
Accepted
Time stored using now()::timestamp function stores wrong value
tl;dr
Instead of the data type TIMESTAMP WITHOUT TIME ZONE, define your column for the data type TIMESTAMP WITH TIME ZONE.
Set the default time zone of your session within PgAdmin to UTC.
To adjust ...
- 11k
9
votes
Accepted
Why does MySQL calculate a wrong timestamp when using FROM_UNIXTIME
MySQL is behaving correctly – your test is invalid.
If you round-trip through a time zone with DST, you will not have a lossless conversion if you hit a transition. The timestamp in question ...
- 22.7k
9
votes
Accepted
Why are there no "Daylight Time" time zones in SQL Server?
The time zone list actually comes from an API call to the OS, But SQL Server doesn't expose all of the columns/attributes available. Unfortunately, this means that we're depending on the design ...
- 16.3k
8
votes
Accepted
SQL Server 2012 equivalent of AT TIME ZONE capability
This is a great use case for a Calendar table.
Aaron Bertrand covers this exact scenario in depth here. The entire article is quite in depth to replicate in an answer here, but I'll try to summarize.
...
- 16.3k
6
votes
Accepted
Clock change on SQL Server
SQL Server just uses the operating system's time. However, if your application code relies on functions like GETDATE(), then your code needs to be able to handle jumps forward (or backward) in time.
...
- 43.2k
6
votes
I can't find time zone tables in sys database of MySQL
I used to face the same issue as you and I resolved it by following these quick steps.
Please follow below:
Environment:
MySQL Version: 5.7
OS: Windows 10 (64 bit)
Steps:
Download the package ...
- 161
6
votes
How do I resolve this error, "ERROR 1298 (HY000): Unknown or incorrect time zone: 'UTC'"?
As kaiser suggested, the trailing slash is unneeded but without specifying mysql as the database mariadb complains about a missing database. I found that
$ mysql_tzinfo_to_sql /usr/share/zoneinfo | ...
- 161
6
votes
Accepted
What is a valid use case for using TIME WITH TIME ZONE?
There is no valid use case. time with time zone is broken by design and only included in Postgres since it's in the SQL standard. Don't use it.
Time zones can have daylight saving time (DST) rules, ...
- 182k
6
votes
Accepted
Incorrect EST Timezone instead of EDT in Oracle
I did not investigate any further for specific database and timezone file versions, but I guess you use and old database version with an outdated timezone file.
10.2.0.5.0:
SQL> select VERSION ...
- 41.1k
6
votes
Any downside to using timestamp without time zone if UTC is ok at all times
If all are aware that the timestamp value is supposed to represent UTC time it should work, basically. A convenient advantage is that default input and output is less noisy.
I would still suggest ...
- 182k
5
votes
Accepted
Why do I see the timezone offset displayed in PgAdmin, for a timestamp with timezone
I'm in Dublin - currently on UTC/GMT.
I did the following:
CREATE TABLE t
(
x TIMESTAMPTZ NOT NULL
);
then
INSERT INTO t VALUES (NOW());
Then in pgAdmin, I SELECT * FROM t and the value I see is
...
- 30.6k
4
votes
Update value in timestamptz column
You fell victim to a cascade of bad ideas.
1. DateStyle
Do not use locale-dependent format in your statements if you can avoid it. If locale settings change (lc_time and DateStyle in particular), ...
- 182k
4
votes
Why does PostgreSQL interpret numeric UTC offset as POSIX and not ISO-8601?
The manual can easily be misread there. Quoting the same source, but with one more leading sentence:
All timezone-aware dates and times are stored internally in UTC. They
are converted to local time ...
- 182k
4
votes
Accepted
How to compare timestamps with different time zones?
This just works:
SELECT *, ts AT TIME ZONE '-01' -- see below about '-1' vs '+1'
FROM (
VALUES
(1, timestamptz '2018-04-05 06:00:00 +01')
, (2, '2018-04-05 06:00:00 +00')
, (3, ...
- 182k
4
votes
Accepted
Preserving timezones explicitly in postgres
My use case is a simple end-user-facing application accessed from only folks in the 'Europe/Berlin' timezone. [...]
My intuition is now to store the time as a timestamp without time zone because [...]
...
- 182k
4
votes
Postgres epoch from current_timestamp
The documentation of extract clearly states:
For timestamp with time zone values, the number of seconds since 1970-01-01 00:00:00 UTC (can be negative); for date and timestamp values, the number of ...
- 56.4k
4
votes
What is the reasoning behind PostgreSQL changing the timezone when changing a timestamp with time zone field?
timestamp with time zone does not quite meet the expectations of the SQL standard. It should more appropriately be called “absolute timestamp”.
It is stored as microseconds since 2000-01-01 00:00:00 ...
- 56.4k
4
votes
Accepted
E. Australia Standard Time appears to be one hour early?
The one you want is "AUS Eastern Standard Time". Don't know what the different definitions are but this has DST and more closely resembles the official names (if you squint a bit).
- 25.1k
4
votes
Accepted
Time zone lost when returning timestamp with time zone from a function
The data type timestamp with time zone (timestamptz) does not store any time zone information. That's a common misconception, inspired by the misleading name. (Blame the SQL standards committee!) You ...
- 182k
4
votes
Accepted
How to query dates in different timezones?
This can use the index:
SELECT *
FROM some_table
WHERE expires_at
< date_trunc('day', (now() AT TIME ZONE 'America/New_York')) AT TIME ZONE 'America/New_York'
-- ORDER BY expires_at --!!?
...
- 182k
4
votes
Accepted
Postgres: why does to_timestamp return the date with different timezones, in the same query?
Austria has daylight savings time, so PostgreSQL will display timestamps in winter with a different UTC offset than timestamps in spring. to_timestamp converts an UTC epoch to a (time zone ...
- 56.4k
4
votes
Accepted
Retrieving the local timezone inside SQLite
You cannot extract the timezone, but you can calculate it.
ROUND((JULIANDAY('now', 'localtime') - JULIANDAY('now')) * 24) -- this gives the number of hours (signed)
ABS(ROUND((JULIANDAY('now', '...
- 1,671
3
votes
Accepted
Olson DB: which version in PostgreSQL?
Currently (Jan 23, 2017)
PostgreSQL uses IANA (Olson) version 2016j
2016j is the latest version.
tz-updates are rolled out to supported major versions in point releases. 9.2 is still supported (...
- 64.7k
3
votes
Accepted
Does AT TIME ZONE automatically take care of DST conversion?
Yes, it accounts for it:
AT TIME ZONE applies specific rules for converting input values in
smalldatetime, datetime and datetime2 data types, that fall into an
interval that is affected by the ...
Only top scored, non community-wiki answers of a minimum length are eligible