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
14
votes
2 answers

Calculate the week number (0-53) in year

I have a dataset with locations and dates. I would like to calculate week of the year as number (00–53) but using Thursday as the first day of the week. The data looks like this: location <- c(a,b,a,b,a,b) date <-…
Eco06
  • 531
  • 2
  • 8
  • 17
13
votes
5 answers

Convert seconds to days, hours, minutes, seconds (MySQL)

Example: seconds ="1015557"; Result should be: 11days 18h:05m:57s How can do this in MySQL?
Zameer Ahmed
  • 131
  • 1
  • 2
  • 7
13
votes
2 answers

Time Difference in Redshift

how to get exact time Difference between two column eg: col1 date is 2014-09-21 02:00:00 col2 date is 2014-09-22 01:00:00 output like result: 23:00:00 I am getting result like Hours Minutes Seconds -------------------- 3 3 20 …
Benny
  • 432
  • 1
  • 6
  • 21
13
votes
3 answers

find the elapsed time between two dates in oracle sql

i need to find the difference between the time in the format hh:mm:ss select msglog.id,max(msglog.timestamp) enddate, min(msglog.timestamp) startdate, enddate - startdate from MESSAGELOG msglog group by id In the abovequery…
suni
  • 131
  • 1
  • 1
  • 3
13
votes
1 answer

Get the number of days between two dates in Oracle, inclusive of the dates

I want to get total number of days between two provided dates. I've tried the below query but didn't get the exact different; the last date is not being included. select (to_date ('15-06-13','dd-MM-yyyy') - to_date('01-02-12','dd-MM-yyyy')) from…
user968441
  • 1,471
  • 9
  • 22
  • 48
13
votes
12 answers

Calculate business days in Oracle SQL(no functions or procedure)

I am trying to calculate business days between two dates in Oracle select. I got to the point when my calculation gives most results correct for given dates (I compare it with NETWORKDAYS in excel) but sometimes it varies from 2 days to -2 days -…
yochim
  • 475
  • 1
  • 4
  • 10
12
votes
6 answers

Time difference in seconds

In a Perl program I have a variable containing date / time in this format: Feb 3 12:03:20 I need to determine if that date is more than x seconds old (based on current time), even if this occurs over midnight (e.g. Feb 3 23:59:00 with current…
Xi Vix
  • 1,381
  • 6
  • 24
  • 43
12
votes
3 answers

jQuery: pass string variable to date object

I have a page with a number of variables that contain a date in string format (yyyy-mm-dd) which originates from using moment.js. Is there a way I can pass such a variable to a Javascript date object resp. to convert it to a Javascript date object…
user2571510
  • 11,167
  • 39
  • 92
  • 138
11
votes
3 answers

SQL: Get records created in time range for specific dates

I have a set of records that were created last week, and from those I want to retrieve only those that were created between 6h45 and 19h15. I have a column creation_date that I can use. How can I do this in sql?
Mouna Cheikhna
  • 38,870
  • 10
  • 48
  • 69
11
votes
3 answers

How can I calculate the number of weeks since a given date in Ruby?

Objective I am trying to calculate the distance in weeks since a given date without jumping through hoops. I'd prefer to do it in plain Ruby, but ActiveSupport is certainly an acceptable alternative. My Code I wrote the following, which seems to…
Todd A. Jacobs
  • 81,402
  • 15
  • 141
  • 199
11
votes
4 answers

Add DATE and TIME fields to get DATETIME field in MySQL

I am trying to get a DATETIME field from a DATE and a TIME field. none of the functions in MYSQL seems useful. Is somebody aware how to do this or that if this can even be done? :)
Anup
  • 113
  • 1
  • 1
  • 5
11
votes
5 answers

How to calculate next Friday at 3am?

How can you calculate the following Friday at 3am as a datetime object? Clarification: i.e., the calculated date should always be greater than 7 days away, and less than or equal to 14.
mpen
  • 272,448
  • 266
  • 850
  • 1,236
11
votes
6 answers

Subtract one hour from datetime rather than one day

I have a datetime column in Oracle (MM/DD/YYYY HH:MM:SS AM/PM) but when I do this: SELECT MAX(D_DTM)-1 FROM tbl1 ...it goes back a day. How do I remove one hour from the column rather than one day? I've also noticed that the datetime records for…
lightweight
  • 3,227
  • 14
  • 79
  • 142
11
votes
5 answers

How to get the closest dates in Oracle sql

For example, I have 2 time tables: T1 id time 1 18:12:02 2 18:46:57 3 17:49:44 4 12:19:24 5 11:00:01 6 17:12:45 and T2 id time 1 18:13:02 2 17:46:57 I need to get time from T1 that are the closest to time from T2. There is no relationship between…
fen1ksss
  • 1,100
  • 5
  • 21
  • 44
11
votes
7 answers

Number of days in a month

I have a monthly amount that I need to spread equally over the number of days in the month. The data looks like this: Month Value ----------- --------------- 01-Jan-2012 100000 01-Feb-2012 121002 01-Mar-2012 123123 01-Apr-2012 118239 I have…
Raj More
  • 47,048
  • 33
  • 131
  • 198
1 2 3
…
73 74