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 · 7 comments
Closed

timestamp_to_str returning incorrectly adjusted results #6640

monetdb-team opened this issue Nov 30, 2020 · 7 comments
Labels
bug Something isn't working normal SQL

Comments

@monetdb-team
Copy link

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

@monetdb-team monetdb-team added bug Something isn't working normal SQL labels Nov 30, 2020
@sjoerdmullender sjoerdmullender added this to the Ancient Release milestone Feb 7, 2022
@alexandresilvawek
Copy link

Hello,

Is this fix already released?

I'm asking because I think I'm having the same problem here.
I'm using the latest downloadable version for Windows and the issue seems to continue to occur.

Thanks.

Alexandre.

@lrpereira
Copy link
Member

Hi @alexandresilvawek ,

I just tried reproducing in the latest released version(September2022) and result is as expected/correct.
Can you provide more details on exactly what version are you running and system info ?

@alexandresilvawek
Copy link

Hi @lrpereira ,

I'm using MonetDB on Windows 10 64 bit.
In the image below it is possible to see the version of MonetDB that I am using and the change that occurs with the "current_timestamp" when it passes through the "timestamp_to_str" function.
image

@sjoerdmullender
Copy link
Member

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.

@alexandresilvawek
Copy link

Hi @sjoerdmullender ,

If I understand correctly, this is normal/expected behavior.

So I would like to ask for some help:
What is the most suitable way to convert and format the current date and time (with my local time zone)?

@lrpereira
Copy link
Member

Hi @alexandresilvawek ,

We had more discussions within the dev team and indeed the timestamp_to_str function needs improvement/fix.
It is a different issue from what it is present in the older comments.
I will create an issue for it and update the status of the fix there.

GH issue

@alexandresilvawek
Copy link

Hi @lrpereira ,

Thank you for your help.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Something isn't working normal SQL
Projects
None yet
Development

No branches or pull requests

4 participants