Skip to content

Extend data types support for UTC dates, i.e. Timestamp Athena type #49

New issue
New issue
Open
#67
Open
Extend data types support for UTC dates, i.e. Timestamp Athena type#49
#67
@ssedano

Description

@ssedano
ssedano
opened on Nov 21, 2020

Hi team,

I'd like to extend data types support for UTC dates, i.e. Timestamp Athena type. My proposal is to create a new option, e.g. useUtcDates, to cast values of columns with type Timestamp to Date Javascript objects. This setting would have a default value of false. The caveat of this setting is that it will use machine-local time, thus this setting is intended for systems configured in UTC. This could be extended to Date Type but it would transform dates into date and times, e.g. '2020-11-18' would become '2020-11-18T00:00:00Z'. I intentionally avoid casting timestamp with time zone Athena type to reduce complexity.

For applications that deal with dates, converting Timestamp columns to Date objects is a common task given that many libraries expect Date objects. Moreover, athena-express hides the type in the result set. This forces developers that wish to operate using dates to access the column by name and explicitly convert values to Date objects.

I'd be willing to submit a patch if this feature request is approved.

Proposed update to documentation:

  • useUtcDates | boolean | (default false) | Cast Timestamp types to Javascript Date objects with times in UTC. Set this option if your code runs in machines configured with UTC as their timezone. Useful when your Timestamp columns are representing UTC date and time and you want to work with Date objects instead of String objects

Activity

ghdna

ghdna commented on Apr 16, 2021

@ghdna
ghdna
on Apr 16, 2021
Owner

I somehow missed responding to this. If you wanna go ahead and add that, I'll be happy to take a look. Just consider any edge cases that might break it.

ghdna

ghdna commented on Apr 25, 2021

@ghdna
ghdna
on Apr 25, 2021
Owner

Actually the more I think about this, the more it can cause more confusion for users since there are several date formats to convert into:
For e.g. Athena's TIMESTAMP datatype will fetch results as 2008-09-15 03:04:05.327
This string can be converted into 5 formats:

  1. new Date() => Mon Sep 15 2008 03:04:05 GMT-0400 (Eastern Daylight Time)
  2. .toUTCString() => "Mon, 15 Sep 2008 07:04:05 GMT"
  3. .toDateString() => "Mon Sep 15 2008"
  4. .toISOString() => "2008-09-15T07:04:05.327Z"
  5. .toTimeString() => "03:04:05 GMT-0400 (Eastern Daylight Time)"

How would AthenaExpress know which format the user cares about? And the bigger question is, should this be something AthenaExpress does or should this functionality be handed over to the user to decide.

ssedano

ssedano commented on Apr 27, 2021

@ssedano
ssedano
on Apr 27, 2021 · edited by ssedano
Author

Thank you for your reply.

should this be something AthenaExpress does or should this functionality be handed over to the user to decide.

Is the right question to ask. Unfortunately, query method doesn't include the data type. Hence why I propose to let the user decide at the time of configuring AthenaExpress. This saves the user from tracking column types separately and casting objects, keeping AthenaExpress' simplicity.

ghdna

ghdna commented on Apr 29, 2021

@ghdna
ghdna
on Apr 29, 2021
Owner

ok i understand. Let me check on the effort required for this

ssedano

ssedano commented on May 3, 2021

@ssedano
ssedano
on May 3, 2021
Author

I would be happy to contribute the patch

Thank you.

ghdna

ghdna commented on May 5, 2021

@ghdna
ghdna
on May 5, 2021
Owner

Sure, please do. I'd be happy to look at it.

ssedano

ssedano commented on Jun 6, 2021

@ssedano
ssedano
on Jun 6, 2021
Author

Apologies for the delay on this. I expect to have a pull request ready in the next two weeks

ssedano
linked a pull request that will close this issue on Aug 2, 2021
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    Participants

    @ssedano@ghdna

    Issue actions
      Extend data types support for UTC dates, i.e. `Timestamp` Athena type · Issue #49 · ghdna/athena-express