Skip to content
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

Closed
monetdb-team opened this issue Nov 30, 2020 · 0 comments
Closed

timestamp_to_str returning incorrectly adjusted results #6640

monetdb-team opened this issue Nov 30, 2020 · 0 comments

Comments

@monetdb-team
Copy link

@monetdb-team monetdb-team commented Nov 30, 2020

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.

  1. I specified an offset of -4 and the result shows -4, but the time was adjusted
  2. The time was adjusted by +5. I'm not sure where this number came from.

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:

  1. Fresh install of monetdb
  2. Open mclient
  3. Run: select timestamp_to_str('2008-03-31 00:00:00', '%Y-%m-%d %H:%M:%S');

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:

Let mktime figure out DST.
This fixes bug #6640.

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).

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
1 participant