The tag has no usage guidance.

learn more… | top users | synonyms

2
votes
1answer
25 views

PostgreSQL won't take new timezone

In Venezuela a timezone change have been undertaken on May 1st 2016. The old offset for the Venezuela timezone was -04:30 and the new one is -04:00. All database servers have been updated with the ...
0
votes
2answers
626 views

Oracle systimestamp result differs in db server and client

I am getting some strange output while executing the below command on db server and on client machine connected to same db server. On DB server SQL> select systimestamp from dual; systimestamp ...
1
vote
1answer
366 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'
1
vote
0answers
12 views

Time series data and utc conversion

I'm storing data in minute, hour, day, month and year buckets. All data is stored in UTC. Let's assume a client is in PDT time (-07:00 UTC) If the client wants to query for the hour sum of 4/23/2016 ...
0
votes
1answer
43 views

Why does Oracle change my date fields?

I have an Oracle 11g XE set up in Moscow, Russia, and therefore on MSK time zone. When a client in the same timezone stores a DATE variable from Java with, say time set to 12:00, the time is changed ...
1
vote
1answer
56 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 ...
15
votes
7answers
53k 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 ...
13
votes
4answers
73k views

How to handle TimeZone properly in SQL Server?

My local development server is in the Middle East, but my production server is in the UK. I need to show the date to the user in their time zone. For example, if a user is in Saudi Arabia then I ...
7
votes
3answers
3k 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 ...
0
votes
0answers
36 views

Oracle from_tz with Russian timezones shifts the input timestamp?

On Oracle 11g, I noticed that using from_tz to attach a TZ to a timestamp gives weird results for Russian timezones, but only after 25th October 2014. select from_tz(cast( '01-JUL-2015 12:00:00' as ...
2
votes
1answer
60 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 ...
1
vote
1answer
54 views

Converting UTC time to timestamptz always adds the +01 offset in Postgres

In Postgres 9.5, I do the following to convert a timestamptz to the UTC timezone: SELECT TIMESTAMP WITH TIME ZONE '2016-02-20 15:15:54.000495+01' AT TIME ZONE 'UTC' this outputs correctly ...
14
votes
1answer
1k views

Why does time zone have such a crazy offset-from-UTC on year 0001 in Postgres?

In Postgres 9.5, I was surprised to see the result seen below while experimenting with year 0001 (no year zero 0000). Offset of -07:52:58? Some example code. Note that I mixed use of TIMESTAMP WITH ...
1
vote
0answers
22 views

The time is wrong in MySQL “data/*.err” file

I use "select now();" in MySQL client, the return time is right, is my system time. But the time in the "data/g1.err" is wrong, the "g1" is my computer name. The time's minute is right, so I think ...
3
votes
2answers
131 views

How to convert timezone in MySQL using the timezone name?

I want to convert date from one timezone to another timezone in MySQL, MySQL query which I am trying is SELECT DATE_FORMAT(CONVERT_TZ('2015-12-18 06:00:00', '+0:00', '-05:00' ),'%b %d %Y %h:%i %p') ...
1
vote
1answer
2k views

MySQL PDO Cannot assign requested address

Can someone help me with this error? [08-Apr-2013 17:44:08 Europe/Berlin] PHP Warning: PDO::__construct(): [2002] Cannot assign requested address (trying to connect via tcp://****:3306) in ...
1
vote
2answers
39 views

Should I convert timezones when moving data to a report server/data warehouse

I'm writing an SSIS Package to take data from a production DB to a reports server/data warehouse. The other databases on my reports server are all in Mountain Time but this DB is in UTC should I ...
0
votes
1answer
58 views

Filter column by records matching a timezone in Postgres

I want to check whether any rows in my column (TIMESTAMP WITH TIME ZONE) are not stored using UTC. Is there a query I can write to match values that are stored with a TZ offset of 1 or 8 or 11 or (not ...
1
vote
0answers
76 views

mysql datetime timezone +0000

I've got a Twitter database and some columns in multiple tables are time-stamps like: Tue Sep 09 23:48:37 +0000 2014 These columns are TEXT so I want to convert them. I read Date and Time Types, ...
0
votes
0answers
60 views

Syntax to change a DB2 timestamp from CST to EST

I am writing a code and the source are two DB2 databases. I see that both of them have a different timestamps. (one shows CST and another is EST). I need to make sure that both of them have similar ...
1
vote
0answers
66 views

Is not having 'named' timezone support common in MySQL?

I'm trying to set the timezone the database uses using: "SET time_zone = 'UTC'; Whilst this works on my development server it doesn't work on my staging server; I get this error: General error: ...
0
votes
1answer
188 views

Forgot to run END_UPGRADE during a Timezone File Upgrade (ORA-56924)

I ran the following to upgrade my timezone file from 4 to 14. The issue was after running the upgrade I forgot to run DBMS_DST.END_UPGRADE and put the DB back up and enabled incoming connections. ...
8
votes
2answers
2k 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) ...
0
votes
1answer
27 views

Returning rows based on time of day respective to their timezone [closed]

I also posted this on Stack Overflow. I got no answers there so I'm hoping someone here might be able to help. I have a table in which every row represents a user, I am also storing the user's ...
0
votes
0answers
42 views

MongoDB: Multiple timezone support

In Mongodb how to support multiple timezones? Say every client has his own timezone. I want to group by dates. Records are in different timezones before aggregation. The report should be aggregated ...
8
votes
1answer
602 views

Best way to get time zone key name from SQL Server

Below is what I pieced together but I wanted to see what other ways are available. SET NOCOUNT ON; GO DECLARE @tz VARCHAR(50) EXEC [master].[dbo].[xp_regread] 'HKEY_LOCAL_MACHINE' ...
3
votes
1answer
58 views

Postgres streaming replication between hosts with different timezone

I am running Postgres 9.1 on Windows 2012 servers. I am using postgres's streaming replication to replicate data between 2 servers. It has so happened that the time zone of the slave has not been set ...
5
votes
2answers
2k 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 ...
1
vote
1answer
327 views

MYSQL Timezone support

We are having a shared hosting plan and they are saying that do provide MYSQL Timezone support in a shared hosting plan. I can create timezone related tables in our database and populate them with ...
0
votes
1answer
997 views

Postgres automatic insert time stamp with time zone (india) [closed]

Is there any way to insert timestamp with zone automatically when query is inserted ? (just like id auto increment as i don't want to pass the the parameters in sql as time its same every where if we ...
1
vote
3answers
3k views

Figuring out database timezone

Does anybody know of a way to alter a time zone on a SQL database? Is there any tool that could find the objects using the date and time?
1
vote
1answer
452 views

Format timezone to +-#### instead of +-##

I am curious if there is a way to configure Postgres (9.1+) to format the TZ part of a "timestamp with time zone" as +-####, just like PHP's date.format method does using "O". PHP Docs: O | ...
3
votes
2answers
6k views

My MySQL server time is not the same as my Server

So a month ago I had purchased a server from a hosting company. The info are; Linux Centos (latest version). I installed ZPanel which installs phpMyAdmin, MySQL and all the other good stuff. ...
3
votes
1answer
1k views

How do you truncate a `date` (not `timestamptz`) to month?

Postgres has date_trunc which operates on timestamp or interval, and: Values of type date and time are cast automatically to timestamp or interval, respectively. In other words we can use ...
2
votes
2answers
11k views

MySQL timestamp timezone handling

Does the timestamp data type represent an instant-in-time or a year-month-date-hour-minute-second value? An instant in time is a moment, like the moment you started reading this, and it can be ...
2
votes
3answers
840 views

Constrain a postgres timestamp to “End of day”?

Given a table named bananas and a timestamp without time zone column named end_time and some psql clients have set timezone to 'UTC' and other psql clients have set timezone to 'US/Eastern' and the ...
1
vote
1answer
1k views

How does Sybase store datatime information?

We have a Sybase database that currently stores datetime values in some of the tables. If we move that server with the Sybase database to another country which is in another time zone, will the ...
1
vote
2answers
229 views

MySQL Timezone Resets after exiting server

I am running this code to set my database's timezone: set time_zone = "US/Eastern" This successfully changes the time to the correct time. However, as soon as I "exit" the database and re-enter, it ...
3
votes
1answer
10k views

How to convert a unix time to PostgreSQL' Timstamp without timezome?

I have a PostgreSQL database running on a server which has its time zone set to India's Time Zone (i.e. UTC +5:30) I have some data in a table which is created like this: CREATE TABLE "CLOUDDATA" ( ...
0
votes
0answers
213 views

Is US/Pacific timezone in MySQL automatically adjusting to DST?

I'd like to define a view in MySQL which will convert some system data always written in UTC to our headquarter time, which is San Francisco. If I use CONVERT_TZ(..., 'UTC', 'US/Pacific'), will it ...
8
votes
2answers
2k 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 ...
1
vote
2answers
592 views

SQL Server and SharePoint time problem

I have created a SharePoint application which has a specific time zone. The Windows server where SQL Server and SharePoint are installed are using same time zone. When I create a new item in list ...
1
vote
2answers
1k views

Get time format in PostgreSQL

If I want get Ubuntu system time, I have two options: $ cat /etc/timezone US/Eastern $ date Sun Sep 15 14:45:02 EDT 2013 How can I find out if PostgreSQL is using utc or a utc offset, such as ...
0
votes
1answer
3k views

Seting timezone per database in MySQL

I wanted to know if MySQL let the admin set a timezone per database ? I have three different databases for three different customers. Each of them being in their own timezone. I would like to load ...
0
votes
1answer
994 views

Creating a job scheduled based on a specific timezone

I have an Oracle database hosted in Europe. I need to create a scheduled job in the database to send email. I need to send email daily at 17:30:00 in India standard time (IST). I know how to send ...
0
votes
1answer
1k views

Timezones in Oracle 10

I have an application that stores its timestamps in Oracle using UTC (Coordinated Universal Time). Another application reads these timestamps from the database, but there is no convenient way to ...
1
vote
1answer
2k views

How to get SQL of single time zone

Trying to get the SQL for a single time zone results in an error message: $ mysql_tzinfo_to_sql /usr/share/zoneinfo America/Sao_Paulo mysql_tzinfo_to_sql: Error reading file '/usr/share/zoneinfo' ...
5
votes
1answer
2k views

Why/how did the behavior of extract(epoch…) from “timestamp without time zone” fields change between PostgreSQL 8.4 and 9.2?

Context: We currently run PostgreSQL 8.4 in production, and are testing 9.2 internally for a future upgrade. Some issues have emerged regarding dates. The servers that run 8.4 versus 9.2 are ...
1
vote
1answer
364 views

How can I change the timezone in MySQL?

In a MySQL DB, I have two varchar fields. One is for date and one is for time. I need to change the time from CST to GMT. In MySQL how do we convert the combination of these date and time fields from ...