1

So, I have two columns which are in unix/ epoch format (in milliseconds). I convert them accordingly:

dateadd(S, TIME_START/1000, '1970-01-01') AS J_Start, 

dateadd(S, TIME_END/1000, '1970-01-01') AS J_End,

I want another column with the differential. However, when I try and do J_Start - J_End, I get an invalid identifier error. Is there any way around this? I've also tried substracting the full conversion syntax from one another but to no avail...

I was thinking of creating a virtual table and then joining it to the original but I would still encounter the aforementioned problem: Generating a virtual column from other, pre-existing one(s)

StriplingWarrior
  • 151,543
  • 27
  • 246
  • 315
YoungboyVBA
  • 197
  • 7

4 Answers4

2

It works for me:

select 5 as a, 1 as b, a-b as c;
+---+---+---+
| A | B | C |
|---+---+---|
| 5 | 1 | 4 |
+---+---+---+

Unless I did not fully understand the question.

If you have your full query with exact error message, it can help.

UPDATE:

Problem in your query is that you quoted "J_Start" and "J_End", but you did not quote them in the alias of DATEADD function. If you do not quote them, Snowflake will default all identifiers to be using all UPPER case, but if you quote them, they will be preserved. So J_Start actually equals to J_START, while "J_Start" will remain as J_Start.

So your query should be:

SELECT 
  MOTORCYLE_REG, 
  STATE, 
  NON_MAINLAND_FLAG, 
  dateadd(S, TIME_START_EPOCH/1000, '1970-01-01') AS J_Start ,
  dateadd(S, TIME_END/1000, '1970-01-01') AS J_End, 
  J_End::date - J_Start::date as J_Diff_in_dates

If you want to find out differences in seconds or milliseconds, you can use datediff or timestampdiff instead.

Eric Lin
  • 1,440
  • 6
  • 9
  • SELECT MOTORCYLE_REG, STATE, NON_MAINLAND_FLAG, dateadd(S, TIME_START_EPOCH/1000, '1970-01-01') AS J_Start ,dateadd(S, TIME_END/1000, '1970-01-01') AS J_End, “J_Start” – “J_End” As Differential And the error i get is SQL compilation error: error line 2 at position 34 invalid identifier '"J_Start" Tried removing parentheses etc, still doesn[t work. – YoungboyVBA Dec 15 '21 at 23:38
  • 2
    this is not the correct answer, because issue expressed is not actually about "can you re-use tokens" which this shows you can, the error is about datetime's not supporting subtraction, which is valid in other DB's – Simeon Pilgrim Dec 16 '21 at 03:06
  • @SimeonPilgrim Based on the comment above, it seems that I was correct about the issue. – Eric Lin Dec 16 '21 at 04:35
  • @EricLin: But would your last "your query should be" code even work? If Snowflake doesn't support date subtraction via the `-` operator, it seems it wouldn't? – StriplingWarrior Dec 16 '21 at 14:23
  • @StriplingWarrior Snowflake does support "-" between dates, but not timestamp. I have updated the answer to cast to DATE before we can "-". Check the doc here: https://docs.snowflake.com/en/sql-reference/functions/datediff.html#syntax – Eric Lin Dec 17 '21 at 02:42
2

You can use datediff to get the differential. I am calculating the diff in days but you can change it as desired. Double quotes wasn't the cause of the error, but you don't really need them. Just use column names that don't collide with reserved keywords.

select datediff(day, dateadd(S, time_start/1000, '1970-01-01'),dateadd(S, time_end/1000, '1970-01-01'))

You could also use simple subtraction, but you'd have to cast the output of dateadd as date first. Note that you can only calculate diff in days with this method. The one above is easier to maintain, and modify for hours, minutes, and other intervals.

select dateadd(S, time_end/1000, '1970-01-01')::date - dateadd(S, time_start/1000, '1970-01-01')::date

And if you really want to re-use column alias in the same select, which I am not a fan of, you can do

select dateadd(S, time_start/1000, '1970-01-01')::date as j_start,
       dateadd(S, time_end/1000, '1970-01-01')::date as j_end,
       j_end-j_start as diff_days

Having said that, if you only need the diff between two dates expressed in milliseconds, it doesn't matter if you use 1970 or 1800. Just do

select floor((time_end-time_start) / (1000 * 60 * 60 * 24)) as diff_days
Radagast
  • 5,102
  • 3
  • 12
  • 27
  • Thanks. It works when I put in the original column names rather than J_Start and J_End. So I can't reference the virtual columns I created? I still have to go back to the original ones from which the virtual ones are generated? – YoungboyVBA Dec 15 '21 at 23:58
  • @YoungboyVBA See my update – Radagast Dec 16 '21 at 00:08
  • Thank you. Appreciate the solution and given it the tick. One thing that I still can't get my head around though: even in your solution, we're still not able to reference the virtual columns directly? I mean, your'e still dividing them by 1000 and applying that Epoch conversion function. I wonder why SQL isn't able to start J_Start post those two things, so they can be reference to make another virtual column? – YoungboyVBA Dec 16 '21 at 00:16
  • @YoungboyVBA It works as Eric demonstrated , but most databases don't support this functionality in the select clause so I actively avoid it. You probably forgot to cast to date before subtracting. You can copy paste run the code here to try https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=289cc71275516104c65d330ad3d365a2 – Radagast Dec 16 '21 at 00:26
  • OK thank you. Would the date needed to be cast to an int before subtraction? So it'd be CAST(J_Start AS int) - CAST (J_End AS Int)? But then I'd have to cast the result to get in minute or hour format? – YoungboyVBA Dec 16 '21 at 00:30
  • You can keep the milliseconds as is and change the first parameter in datediff to hour or minute. Feel free to re-use column aliases inside the function. Or you can just just tweak the math in the last solution a little bit to get the diff in hours and minutes. For reference see here https://stackoverflow.com/a/45146964/9947159 – Radagast Dec 16 '21 at 00:39
  • 1
    I appreciate the completeness of this answer, but the code snippets need to be updated for correctness. You're using j_start where I would expect to see TIME_START, and you have `b - a` in a snippet where no `b` or `a` are defined. – StriplingWarrior Dec 16 '21 at 14:20
1

in snowflake you can reference the named object of the SELECT section in the other sections, and the parse most "gets what you mean", thus in snowflake:

SELECT 
     '2021-12-16' AS date_string,
     to_date(date_string) as datetime_a,
     dateadd('day', 1, date_time_a) AS a_day_later,
     date_diff('hours', datetime_a, a_day_later);

is just fine, and will give the results of:

"2021-12-16", 2021-12-16, 2021-12-17, 24

thus in the SQL I gave you on your location question, I was refering to things just declared.

The error you are seeing is the fact the subtraction of date's is not supported, because what format do you want the answer in? Thus the use of date_diff

Simeon Pilgrim
  • 22,906
  • 3
  • 32
  • 45
0

We can use existing column aliases and build some calculations .Below is example using your var's:

  select
    to_timestamp('2021-01-01T00:00:00')::timestamp as "J_Start" ,to_timestamp('2021-02-01T00:00:00')::timestamp as "J_end",
    date_part(epoch_second, "J_Start") as "Epcoh_JSTART",
    date_part(epoch_second, "J_end") as "Epcoh_JEND",
    "Epcoh_JSTART" - "Epcoh_JEND";
Anand
  • 514
  • 3
  • 5