Stack Overflow
Stack Overflow

Questions tagged [date-arithmetic]

The term 'date-arithmetic' refers to determining via code such information as how many days, hours, etc. between two calendar dates, and determining the last day of the month, or whether a date is a weekday or a weekend. Be sure to also include the appropriate programming or database tag.

Many computer languages provide for date arithmetic, including , , and languages. Most languages store data with a type of date internally as a number of days since a certain date, such as January 1st 1970 for Unix.

Typical Considerations

  • Basic calculations such as number of days between two dates, number of months between two dates, etc.
  • Scheduling problems often involve how to determine the fifth business day of the month, the last day of the month, the fourth Thursday of the month
  • Regional or Client-specific concerns include whether certain days are holidays, etc.
  • Week number of the year (ISO: Monday as first day of week, Unix: Sunday as first day of week)
1101 questions
6
votes
2 answers

How to add a running count to rows in a 'streak' of consecutive days

Thanks to Mike for the suggestion to add the create/insert statements. create table test ( pid integer not null, date date not null, primary key (pid, date) ); insert into test values (1,'2014-10-1') , (1,'2014-10-2') , (1,'2014-10-3') ,…
Ben
  • 10,931
  • 9
  • 38
  • 47
6
votes
3 answers

ORA-00932: inconsistent datatypes: expected INTERVAL DAY TO SECOND got CHAR

SELECT COALESCE ( (to_timestamp( '2014-09-22 16:00:00','yyyy/mm/dd HH24:MI:SS') - ('2014-09-22 09:00:00' ,'yyyy/mm/dd HH24:MI:SS')) - (to_timestamp( '2014-09-22 16:00:00','yyyy/mm/dd HH24:MI:SS') -…
Thush
  • 975
  • 3
  • 8
  • 11
6
votes
1 answer

Can you define a custom "week" in PostgreSQL?

To extract the week of a given year we can use: SELECT EXTRACT(WEEK FROM timestamp '2014-02-16 20:38:40'); However, I am trying to group weeks together in a bit of an odd format. My start of a week would begin on Mondays at 4am and would conclude…
JM4
  • 6,740
  • 18
  • 77
  • 125
6
votes
5 answers

How to add a time interval to an NSDate?

I have an NSDate and a duration. I need to get the time after the duration Given: The date is "2010-02-24 12:30:00 -1000" duration is 3600 secs I need to get "2010-02-24 13:30:00 -1000" I thought dateWithTimeIntervalSinceReferenceDate:, would do…
user278859
  • 10,379
  • 12
  • 51
  • 74
6
votes
4 answers

Select query for two weeks ago

In my database table I have a field for date (varchar field to save date in yy-mm-dd format ), now I want to select records for two weeks ago. How can i do it ?
Vikram
  • 3,171
  • 7
  • 37
  • 67
6
votes
4 answers

Number of days between two dates - ANSI SQL

I need a way to determine the number of days between two dates in SQL. Answer must be in ANSI SQL.
David Wright
  • 235
  • 1
  • 4
  • 8
6
votes
3 answers

Joda Time - Get all weeks of a year

is there a way to get all weeks of a year plus start and ending days of every week ? (With Joda-Time) something like this (2012) : week : 21 start: 21.05.2012 ending : 27.05.12 Thanks for your help
user1412715
  • 63
  • 1
  • 3
5
votes
2 answers

How to truncate date in postgres?

I am using below condition to truncate date in postgres to_date(to_char(trunc(appointment_date),'YYYYMMDD')||appointment_end_time,''YYYYMMDDHH24:MI:SS')AS tq How I can use this in postgres ?
v bhosale
  • 63
  • 1
  • 1
  • 5
5
votes
3 answers

Distributing time in interval buckets

I have the following table: CREATE TABLE f_contact ( agent character varying, datetimeconnect timestamp without time zone, datetimedisconnect timestamp without time zone, duration integer ); duration is time in seconds between the…
urbanmojo
  • 737
  • 3
  • 12
  • 21
5
votes
4 answers

How can i get number of quarters between two dates in PostgreSQL?

I have MS SQL function DATEDIFF SELECT DATEDIFF(QQ, 0, '2018-09-05') that returns 474(integer). What is PostgreSQL equivalent of this function ?
Roman Martyshchuk
  • 195
  • 1
  • 2
  • 7
5
votes
4 answers

Find current time interval in python?

How can I find the nearest 15 (or 10) minute interval in python ? e.g. >>> datetime.datetime.now() datetime.datetime(2011, 2, 22, 15, 43, 18, 424873) I'd like the current 15 minute interval (15:30-15:44) so I'd like to transform the above datetime…
Habalusa
  • 1,770
  • 3
  • 15
  • 15
5
votes
6 answers

How can I convert a week (200851) into a date (2008-12-27)?

How could I convert a year-week (for example, 0852 or 200852) into a date (for example, 2008-12-31 or specifically a week-ending day, that is, Saturday 2008-12-27 or a week-beginning day, that is, 2008-12-21)? Any day of the week-ending will do,…
Fet
  • 728
  • 3
  • 19
  • 33
5
votes
1 answer

Properly handle TIME WITH TIME ZONE in PostgreSQL

We have a table that is filled with data from a legacy report of another system. The columns of that table reflect the same structure of the report. Here are a abbreviated structure of the table: CREATE TABLE IF NOT EXISTS LEGACY_TABLE ( …
Luiz
  • 325
  • 7
  • 28
5
votes
2 answers

Difference in time between records

I have a table that has (among others) a timestamp column (named timestamp; it's a standard Oracle DATE datatype). The records are about 4-11 minutes apart, about 7 or 8 records every hour, and I'm trying to determine if there is any pattern to…
AndyDan
  • 749
  • 2
  • 13
  • 29
5
votes
4 answers

Is there an inverse of the yday lubridate function?

I have a list of days in the format "day of the year" obtained by applying lubridate::yday() function to a list of dates. For instance, from the following dates (mm-dd-yyyy format): 01-01-2015 01-02-2015 ... by applying yday() you get 1 2 ... Is…
mickkk
  • 1,172
  • 2
  • 17
  • 38
1 2 3
…
73 74