current_timestamp-1 in dataframe

Question

current_timestamp-1 in dataframe

Shambhu Rai 1,411

Hi Expert

i wanted to fetch the data for last one hour in databricks dataframe

i tried below filter on top of select statement but gives a error integer ca not bee converted .. how we can get last one hour data in dataframe

.filter(df['Updatedtime'].between
( current_timestamp(),1 )

Subashri Vasudevan 11,306 Reputation points Volunteer Moderator

2023-12-02T15:31:04.5333333+00:00

Hi, @Shambhu Rai just checking to see if you were able to achieve the above. If yes and the below answer helped, request you to accept it as answer..if you have followed different way, request you to post it so that community with similar requirement will be benefited.

Thanks

1 answer

Your answer

Subashri Vasudevan 11,306 Reputation points Volunteer Moderator

2023-12-02T15:31:04.5333333+00:00

Hi, @Shambhu Rai just checking to see if you were able to achieve the above. If yes and the below answer helped, request you to accept it as answer..if you have followed different way, request you to post it so that community with similar requirement will be benefited.

Thanks

Answer 1

Subashri Vasudevan 11,306 Volunteer Moderator

Hi
Shambhu Rai

Please try this expression

filter(df['Updatedtime'].between( current_timestamp(),current_timestamp()-1)

In your case, you are passing current_timestamp and 1 to the between function and hence you are getting the error. We need to pass two valid timestamps for it to work fine.

Kindly try above expression and let us know if that works.

Thanks

Shambhu Rai 1,411

Error Message

 File <command-3632695101123850>:2
    (current_timestamp(),current_timestamp()-1).show()
                                                       ^
SyntaxError: incomplete input

Subashri Vasudevan 11,306 Reputation points Volunteer Moderator

2023-11-29T14:04:46.0133333+00:00

Please try this

df.filter(df['datetime'].between(current_timestamp(),current_timestamp()-expr("INTERVAL 1 HOURS")))
.show(truncate=False)
Shambhu Rai 1,411 Reputation points

2023-11-29T14:30:15.58+00:00

Again error

file_list = dbutils.fs.ls("dbfs:/mnt/test/test/test1")

display(file_list)

df=df.select('path','name','size',(df.moditime/1000).cast('timestamp').alias('Updatedtime'))

df=df.select('path','name','size','Updatedtime').filter(df['Updatedtime']>=

current_timestamp()).show()

df6df.select('path','name','size','Updatedtime').filter(df['Updatedtime'].between(current_timestamp(),current_timestamp()-expr("INTERVAL 1 HOURS")))

error

AttributeError Traceback (most recent call last)

File <command-3632695101123995>:1

----> 1 df6=df.select('path','name','size','Updatedtime').filter(df['Updatedtime'].between(current_timestamp(),current_timestamp()-expr("INTERVAL 1 HOURS"))
Subashri Vasudevan 11,306 Reputation points Volunteer Moderator

2023-11-29T15:18:24.5933333+00:00

Hi Shambhu Rai,

Updated comment:

It works fine for me. Looks like there's a syntax error in your code. Please try running one by one line and isolate error. Try the same way how I have shared (don't use select columns), directly use df.filter in the same way i have suggested as below

df6=df.filter(df['Updatedtime'].between(current_timestamp(),current_timestamp()-expr("INTERVAL 1 HOURS"))

Try to remove other line given below which is not working.

df=df.select('path','name','size','Updatedtime').filter(df['Updatedtime']>=

current_timestamp()).show()

Please make these changes and let me know if that works.
Shambhu Rai 1,411 Reputation points

2023-12-02T16:43:08.04+00:00

Based on this condition I am tried moving files from one container to another but not working
Shambhu Rai 1,411 Reputation points

2023-12-04T18:28:13.6766667+00:00

suggestion pls
Shambhu Rai 1,411 Reputation points

2023-12-12T00:14:01.6133333+00:00

suggestion pls

Share via

current_timestamp-1 in dataframe

1 answer

Your answer