Pyspark date to timestamp with zero hours / midnight

Question

I'm attempting to create a datetime variable for the start of the day. Ultimately, so I can pass it into various queries (like example below) via an f string.

import datetime 

start_datetime = datetime.datetime.today()

query = f""" (SELECT *
             FROM   TABLE_NAME
             WHERE  datetime_var >= '{start_datetime}'
             )"""

I'm having trouble working out how to get zero hours into the datetime/timestamp.

This is what I've tried:

from pyspark.sql.functions import to_timestamp

# attaching '00:00:00' to the date
start_date = datetime.date.today()
start_dt = to_timestamp(str(start_date)+' 00:00:00')

print(start_dt)
>>> Column<b'to_timestamp(`2020-02-10 00:00:00`)'>

This doesn't resolve into a value. I've not been able to work out what else to try.

How can I get the midnight of the current day timestamp?

Thanks in advance.

Check if this can resolve your issue -https://stackoverflow.com/questions/373370/how-do-i-get-the-utc-time-of-midnight-for-a-given-timezone — Ankur Chavda, Feb 10 '20 at 12:19
Thanks @AnkurChavda. Looks like the `.replace()` function was what i was after :) — bumblebear, Feb 10 '20 at 13:54

score 1 · Answer 1 · answered Feb 10 '20 at 13:52

Using what you already imported :

import datetime 
from pyspark.sql.functions import to_timestamp

start_datetime = str(datetime.date.today().strftime('%Y-%m-%d %H:%M:%S'))
query = f""" (SELECT *
             FROM   TABLE_NAME
             WHERE  datetime_var >= '{start_datetime}'
             )"""
print(start_datetime)
>>> 2020-02-10 00:00:00

score 0 · Answer 2 · answered Feb 10 '20 at 13:57

Thanks @AnkurChavda for posting that link. Here's how to do it.

import datetime 

# replace hour, minute, second & microsecond values 
start_dt = datetime.datetime.today().replace(hour=0, minute=0, second=0, microsecond=0)

print(start_dt)
>>> 2020-02-10 00:00:00

Pyspark date to timestamp with zero hours / midnight

2 Answers2