3

I currently have tables with dates, set up as VARCHAR in the format of YYYY-MM-DD such as: 

2017-01-01

The date column I'm working with is called 'event_dt'

I'm used to being able to use day(event_dt), month(event_dt), year(event_dt) etc. in Hive, but Presto just gives me error executing query with no other explanation when the queries fail.

So, for example, I've tried: 

select
month(event_dt)
from
my_sql_table
where
event_dt = '2017-01-01'

I would expect the output to read: 

01

but all I get is [Code: 0, SQL State: ] Error executing query

I've tried a few other methods listed in the Presto documentation but am having no luck at all. I realize this is probably very simple but any help would be much appreciated.

DanB
  • 2,022
  • 1
  • 12
  • 24
phenderbender
  • 625
  • 2
  • 8
  • 18
  • See that post : https://stackoverflow.com/questions/39880540/presto-sql-converting-a-date-string-to-date-format. You have first to convert your varchar to a date type. – DanB Nov 08 '19 at 19:32
  • `where event_dt = date '2017-01-01'` ? – LukStorms Nov 08 '19 at 19:39
  • @LukStorms yes I'm able to specify in my where clause using that, as well as a range such as 'between '2017-01-01' and '2017-03-03' but I'm also looking for ways to have my output give a column with year/month/day etc included – phenderbender Nov 08 '19 at 19:41
  • oh, that date column is actually a varchar. Does something like this give an error? `select month(cast('2019-10-15' as date))` – LukStorms Nov 08 '19 at 19:45
  • @LukStorms yes! Thank you, that worked. Are you able to point me to a specific piece of documentation you used to find that? – phenderbender Nov 08 '19 at 19:49
  • There's some documentation [here](https://prestodb.github.io/docs/current/functions/datetime.html). But CAST is standard SQL that works in almost any DBMS, so I figured presto would also know it – LukStorms Nov 08 '19 at 19:59
  • @LukStorms I'm also trying to sum a field 'revenue' which is a BIGINT datatype, but simply using sum(revenue) isn't working - is there some type of CAST I need to be doing here as well? I thought you could just sum bigints? – phenderbender Nov 08 '19 at 20:08
  • Well, I don't actually know presto, nor know some online tester for it. But from other SO posts I believe it should work if it's really a BIGINT. If it's a varchar then cast it to bigint? Also, presto should even have the [TRY_CAST](https://prestodb.github.io/docs/current/functions/conversion.html) function, which would return a NULL on invalid tekst instead of crashing on that. – LukStorms Nov 08 '19 at 20:23

2 Answers2

4

You can use the month() function after converting the varchar to a date with the date() function:

presto> select month(date('2017-01-01'));
 _col0
-------
     1
(1 row)
Martin Traverso
  • 4,731
  • 15
  • 24
1

Thanks to @LukStorms in the comments to the original question, I've found two solutions:

  1. Using month(cast(event_dt as date))
  2. Using month(date(event_dt))
phenderbender
  • 625
  • 2
  • 8
  • 18