New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
timestamp_to_str returning incorrectly adjusted results #6640
Comments
Hello, Is this fix already released? I'm asking because I think I'm having the same problem here. Thanks. Alexandre. |
Hi @alexandresilvawek , I just tried reproducing in the latest released version(September2022) and result is as expected/correct. |
Hi @lrpereira , I'm using MonetDB on Windows 10 64 bit. |
The server runs on UTC (aka GMT), and so that is the time that is getting converted to a string. The timestamp_to_str function does not know your time zone, so it uses UTC. The cast to string is done at a different level which does know your time zone, so it converts the time to your local time zone. See the -03:00 in the first column. |
Hi @sjoerdmullender , If I understand correctly, this is normal/expected behavior. So I would like to ask for some help: |
Hi @alexandresilvawek , We had more discussions within the dev team and indeed the timestamp_to_str function needs improvement/fix. |
Hi @lrpereira , Thank you for your help. |
Date: 2018-09-04 21:44:49 +0200
From: cedric
To: SQL devs <>
Version: 11.31.7 (Aug2018)
Last updated: 2018-10-17 10:07:09 +0200
Comment 26609
Date: 2018-09-04 21:44:49 +0200
From: cedric
User-Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/69.0.3497.81 Safari/537.36
Build Identifier:
Hi,
This is my first bug report here so if I am doing anything wrong please let me know. It's possible this is not a bug, but the behavior is unexpected and I have not been able to find any documentation or existing bug reports that might explain it.
In mclient, I get the following behavior:
sql>select timestamp_to_str('2008-03-31 00:00:00', '%Y-%m-%d %H:%M:%S');
+---------------------+
| L2 |
+=====================+
| 2008-03-31 01:00:00 |
+---------------------+
sql>select timestamp_to_str('2008-01-31 00:00:00', '%Y-%m-%d %H:%M:%S');
+---------------------+
| L2 |
+=====================+
| 2008-01-31 00:00:00 |
+---------------------+
Why does the hour get shifted by 1 for March 31, but not January 31? Is there a timezone conversion going on? I am running Ubuntu 18.04 with the following timezone settings:
$ date
Tue Sep 4 15:31:23 EDT 2018
$ date +"%Z %z"
EDT -0400
$ cat /etc/timezone
America/New_York
mclient shows the correct timezone offset:
sql>SELECT NOW;
+----------------------------------+
| L2 |
+==================================+
| 2018-09-04 15:35:46.000000-04:00 |
+----------------------------------+
I tried explicitly setting the timezone in the query:
sql>select timestamp_to_str('2008-03-31 00:00:00-04:00', '%Y-%m-%d %H:%M:%S %z');
+---------------------------+
| L2 |
+===========================+
| 2008-03-31 05:00:00 -0400 |
+---------------------------+
I am also confused by this result.
I tried setting the timezone as per the documentation:
sql>SET TIME ZONE INTERVAL '-04:00' HOUR TO MINUTE;
operation successful
But I still encountered the same behavior:
sql>select timestamp_to_str('2008-03-31 00:00:00-04:00', '%Y-%m-%d %H:%M:%S %z');
+---------------------------+
| L2 |
+===========================+
| 2008-03-31 05:00:00 -0400 |
+---------------------------+
Let me know if there is any other information I can provide or some documentation I may have overlooked.
Thanks,
Cedric
Reproducible: Always
Steps to Reproduce:
Actual Results:
+---------------------+
| L2 |
+=====================+
| 2008-03-31 01:00:00 |
+---------------------+
Expected Results:
+---------------------+
| L2 |
+=====================+
| 2008-03-31 00:00:00 |
+---------------------+
$ mserver5 --version
MonetDB 5 server v11.31.7 "Aug2018" (64-bit, 128-bit integers)
Copyright (c) 1993 - July 2008 CWI
Copyright (c) August 2008 - 2018 MonetDB B.V., all rights reserved
Visit https://www.monetdb.org/ for further information
Found 7.7GiB available memory, 4 available cpu cores
Libraries:
libpcre: 8.39 2016-06-14 (compiled with 8.39)
openssl: OpenSSL 1.1.0g 2 Nov 2017 (compiled with OpenSSL 1.1.0g 2 Nov 2017)
Compiled by: xxx@xxx (x86_64-pc-linux-gnu)
Compilation: gcc -g -O2
Linking : /usr/bin/x86_64-linux-gnu-ld -m elf_x86_64 -Wl,-Bsymbolic-functions
$ date
Tue Sep 4 15:31:23 EDT 2018
$ date +"%Z %z"
EDT -0400
$ cat /etc/timezone
America/New_York
$ lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description: Ubuntu 18.04.1 LTS
Release: 18.04
Codename: bionic
Comment 26614
Date: 2018-09-10 14:02:03 +0200
From: MonetDB Mercurial Repository <>
Changeset 1b0865a8ae45 made by Sjoerd Mullender sjoerd@acm.org in the MonetDB repo, refers to this bug.
For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=1b0865a8ae45
Changeset description:
Comment 26615
Date: 2018-09-10 14:34:49 +0200
From: @sjoerdmullender
Thanks for the bug report. As you can see, I fixed the problem.
The difference between the two timestamps was that one was using DST (31 March) and one wasn't (31 January).
The text was updated successfully, but these errors were encountered: