Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Getting the date of the last day of this [week/month/quarter/year]

Is there any way to get the date (a datetime, pd.Timestamp or equivalent) of the last day of this [week/month/quarter/year] with datetime, pandas or other date & time utils?

like image 363
Amelio Vazquez-Reina Avatar asked Aug 13 '15 19:08

Amelio Vazquez-Reina


People also ask

Which formula will you use to populate the date for the last day of the month for ordering?

=EOMONTH(A2, 1) - returns the last day of the month, one month after the date in cell A2. =EOMONTH(A2, -1) - returns the last day of the month, one month before the date in cell A2. Instead of a cell reference, you can hardcode a date in your EOMONTH formula.

How do I get the first and last date of a month in Excel?

Here, we use the EOMONTH function to go to the last day of the previous month. Then, we add 1 to get the first day of the current month. To perform the previous example with the EOMONTH function, we need to use the formula =EOMONTH(A2,-1)+1 in cell B2.

How do I get the last week start and end date in python?

You can use datetime. timedelta for that. It has an optional weeks argument, which you can set to -1 so it will subtract seven days from the date . You will also have to subract the current date's weekday (and another one, to even the day since the Monday is 0 ).

How do you find the last date in the month of a snowflake?

Get the last day of the current month as a DATE value using the DATEADD and DATE_TRUNC functions: SELECT DATEADD('day',-1, DATE_TRUNC('month', DATEADD(day,31,DATE_TRUNC('month',current_date()) ) ) ); Alternative option.


2 Answers

Using datetime only.

>>> d = datetime.date.today()

Last day of week:

# This was wrong earlier.
>>> d + datetime.timedelta(days=5 - d.weekday())
datetime.date(2015, 8, 15)

Last day of month:

>>> datetime.date(year=(d.year + int(d.month % 12 == 0)), month=(d.month + 1) % 12, day=1) - datetime.timedelta(days=1)
datetime.date(2015, 8, 31)

Last day of quarter:

>>> datetime.date(year=d.year, month=((d.month % 3) + 1) * 3 + 1, day=1) - datetime.timedelta(days=1)
datetime.date(2015, 9, 30)

Last day of year:

>>> datetime.date(year=d.year, month=12, day=31)
datetime.date(2015, 12, 31)

EDIT: This is all pretty ugly and using a higher level third party library is probably best, unless there is a compelling reason not to (and there does not seem to be here).

like image 101
Ben Avatar answered Nov 12 '22 14:11

Ben


You could add a pandas.DateOffset to a DateTimeIndex, Timestamp or datetime.date or datetime.datetime:

dates = pd.date_range('2015-8-13', periods=4, freq='3D')
# DatetimeIndex(['2015-08-13', '2015-08-16', '2015-08-19', '2015-08-22'],
# dtype='datetime64[ns]', freq='3D', tz=None)

Snap to the last day of the week (for example, Sunday):

In [232]: dates+offsets.Week(weekday=6)
Out[232]: DatetimeIndex(['2015-08-16', '2015-08-23', '2015-08-23', '2015-08-23'], dtype='datetime64[ns]', freq=None, tz=None)

Snap to the last day of the month:

In [207]: dates+offsets.MonthEnd()
Out[207]: DatetimeIndex(['2015-08-31', '2015-08-31', '2015-08-31', '2015-08-31'], dtype='datetime64[ns]', freq=None, tz=None)

Snap to the last day in the quarter:

In [212]: dates+offsets.QuarterEnd()
Out[215]: DatetimeIndex(['2015-09-30', '2015-09-30', '2015-09-30', '2015-09-30'], dtype='datetime64[ns]', freq=None, tz=None)

Snap to the last day of the year:

In [219]: dates+offsets.YearEnd()
Out[222]: DatetimeIndex(['2015-12-31', '2015-12-31', '2015-12-31', '2015-12-31'], dtype='datetime64[ns]', freq=None, tz=None)

Notice that adding an offset always advances the date. For example, 2015-08-16 is a Sunday, and adding an offsets.Week(weekday=6) advances it it 2015-08-23:

In [233]: pd.Timestamp('2015-8-16')+offsets.Week(weekday=6)
Out[233]: Timestamp('2015-08-23 00:00:00')

To prevent that from happening, you could subtract one day from dates:

In [234]: dates - offsets.Day() + offsets.Week(weekday=6)
Out[237]: DatetimeIndex(['2015-08-16', '2015-08-16', '2015-08-23', '2015-08-23'], dtype='datetime64[ns]', freq=None, tz=None)
like image 43
unutbu Avatar answered Nov 12 '22 14:11

unutbu