Error while writing query of existing query

#1

Hey,
I am writing this query using existing query.

select query_1018.user, CAST(Date_1 as DATETIME), Date_2 , (Date_2 - Date_1) from query_1018
inner join query_1021 on query_1021.user = query_1018.user

But as soon as I use CAST function to convert string value of output of the existing query to datetime.
It will return year value.

Basically I need to know how I can convert string to datetime and perform operation on them using values for existing query.

As per my understanding when you use values from existing query then it return every value as string datatype.

Please correct me if I am wrong and I need a help regarding this.

#2

QRDS run on SQLite. Before it can read your date, you need to pre-format your date string as ISO8601. Something like: YYYY-MM-DD HH:MM:SS or YYYY-MM-DD.

Then you can use strftime() function to format your date as you like.

Example:
date_1 = 2019-01-01
strftime(‘%Y’, date_1) = 2019

Usually, I convert my date_1 into unixepoch first, and let strftime know that the date is on unixepoch format.

Example:
date_1 = 2019-01-01
date_1_epoch = extract (epoch from date_1) = 1546300800
strftime(‘%Y’, date_1_epoch, ‘unixepoch’) = 2019

If you want to get datetime, you can use this format datetime(date_1_epoch, ‘unixepoch’) or
strftime(’%Y-%m-%d %H:%M:%S’, date_1_epoch, ‘unixepoch’)

Read more here

2 Likes
#3

Thanks for the help.

But what I also required is to take date difference between two date.
date_1 - date_2. Since existing query uses date as string format.
I am unable to calculate date difference between two dates.

#4

Then you just need to calculate epoch differences between two dates, and convert it to day.

Example:
date_1 = 2019-01-01
date_1_epoch = extract (epoch from date_1) = 1546300800
date_2 = 2019-02-01
date_2_epoch = extract (epoch from date_2) = 1548979200

To get days differences between date_2 and date_1 you just need to subtract both dates in epoch format and divide by 86400 (number of seconds in a day):

(date_2_epoch - date_1_epoch)/86400 = (1548979200 - 1546300800)/86400 = 31

Hope it helps!

#5

It’s giving an Error running query: near “from”: syntax error.

Does the above logic work in sql also.

If so can help me to write the syntax for it.

#6

May I know what you wrote on your SQL editor? If it’s syntax error, maybe it’s some kind of error in writing the queries.

It should works on SQLite and the Query Result data source is using SQLite

#7

select query_1023.user, date_1, date_2,
extract(epoch from date_1) as date_epoch from query_1023
inner join query_1024 on query_1023.user = query_1024.user

This is my query.

All I have to do is to take a difference between date_1 and date_2
And I am running this query on existing query.

#8

What is the example values for column date_1 and date_2?

#9

date_1 = 25/09/19 13:u5408
date_2 = 27/10/19 08:48

#10

date_1 = 25/09/19 13:54
date_2 = 27/10/19 08:48

Hi,
These are the two values that I am getting from query as an output for date_1 and date_2

#11

Ah I see, you had to format it first as I mentioned previously in your query 1023, or you could transform it to epoch using extract(epoch from date_1) in your query 1023. You can’t transform the date on using query from existing result, since it couldn’t read your current date format.

It should be like: yyyy-mm-dd hh:mm
and not yyyy/mm/dd hh:mm