Add Day to Timestamp

Adding days to a timestamp

Never use math like 60*60*24*7 to add/subtract days (because of daylight time saving), use strtotime or mktime instead:

$timestamp = strtotime('+7 days', $timestamp);

// Or something like this (it's OK to have day parameter <= 0 or > 31)
$timestamp = mktime(0, 0, 0, $month, $day + 7, $year);

Your example will be more obvious if you'll output time as well:

$timestamp = mktime(0, 0, 0, 10, 28, 2010);
echo date('Y-m-d H:i:s', $timestamp) . "\n";

$timestamp += 60*60*24*7;
echo date('Y-m-d H:i:s', $timestamp) . "\n";

Output:

2010-10-28 00:00:00
2010-11-03 23:00:00

Here you have 2010-11-03 23:00:00 instead of 2010-11-04 00:00:00 because one of the days (31 Oct) is 25 hours long instead of 24.

Add days to a timestamp

strtotime() converts the date into a unix timestamp which is the number of seconds since January 1st 1970. If you want a date output you have to run the finished timestamp through date() first.

$capturedDate = '2008-06-20'; 
$endDate = strtotime($capturedDate.' +1 day'); 
echo date("Y-m-d", $endDate);

How to add days to a TIMESTAMP?

Here's a solution that should work for you. The function module that should be available in your system is TIMESTAMP_DURATION_ADD.

REPORT zzz.

DATA lv_time TYPE timestamp VALUE '20180228000000'.

START-OF-SELECTION.
  DATA timestamp_out TYPE timestamp.

  CALL FUNCTION 'TIMESTAMP_DURATION_ADD'
    EXPORTING
      timestamp_in    = lv_time
      timezone        = 'UTC'
      duration        = 1
      unit            = 'TAG' " day (in German)
    IMPORTING
      timestamp_out   = timestamp_out
    EXCEPTIONS
      timestamp_error = 1
      OTHERS          = 2.

  ASSERT sy-subrc = 0.

  WRITE timestamp_out.

Adding days to a date in Python

The previous answers are correct but it's generally a better practice to do:

import datetime

Then you'll have, using datetime.timedelta:

date_1 = datetime.datetime.strptime(start_date, "%m/%d/%y")

end_date = date_1 + datetime.timedelta(days=10)

Add X days to timestamp

You can use DateTime to do the calculations and convert that to a timestamp:

var startDate = DateTime.Now;
long startTimeStamp = startDate.ToTimeStamp();

var endDate = startDate.AddDays(1);
long endTimeStamp = endDate.ToTimeStamp();

There are various way to make a "timestamp" from a DateTime (depending on your definition of timestamp), so pick any that works and move that to a DateTime extension method as demonstrated above:

public static class DateTimeExtensions
{
    public static long ToTimeStamp(this DateTime input)
    {
        // your implementation here
    }
}

For implementations see Function that creates a timestamp in c#, How to convert datetime to timestamp using C#/.NET (ignoring current timezone), How to get the unix timestamp in C#, and so on.

Add days to Timestamp column

I'm losing the hours from the original timestamp column after adding days

That's because date_add always returns DateType (rather than TimestampType), even though it takes either type as input.

A couple of approaches using Spark API if using of UDF isn't preferred:

Approach 1: Convert to Unix epoch time and modify in seconds (*)

Df.
  withColumn("startDt", to_timestamp($"startDt")).
  withColumn(
    "newdate",
    when($"Flag".isNotNull && $"Flag" > 0 && $"Flag" <= 30 && $"startDt".isNotNull,
      to_timestamp(unix_timestamp($"startDt") + $"Flag" * 3600 * 24)
    )
  ).show
// +---+-------------------+----+-------------------+
// | Id|            startDt|Flag|            newdate|
// +---+-------------------+----+-------------------+
// |  1|2020-04-01 14:31:10|   0|               null|
// |  2|2020-04-15 14:31:10|  -1|               null|
// |  3|2020-04-16 03:31:10|   3|2020-04-19 03:31:10|
// |  6|2020-03-01 14:31:10|  30|2020-03-31 15:31:10|  <-- +1 hour due to daylight savings
// +---+-------------------+----+-------------------+

(*) If the period of the added/subtracted days crosses a Daylight saving time, the hour value will be shifted.

Approach 2: Split the datetime string and modify date & time separately

Df.
  withColumn("splitTS", split($"startDt", "\\s+")).
  withColumn(
    "newdate",
    when($"Flag".isNotNull && $"Flag" > 0 && $"Flag" <= 30,
      concat(expr("date_add(to_date(splitTS[0]), Flag)"), lit(" "), $"splitTS"(1))
    )
  ).show
// +---+-------------------+----+--------------------+-------------------+
// | Id|            startDt|Flag|             splitTS|            newdate|
// +---+-------------------+----+--------------------+-------------------+
// |  1|2020-04-01 14:31:10|   0|[2020-04-01, 14:3...|               null|
// |  2|2020-04-15 14:31:10|  -1|[2020-04-15, 14:3...|               null|
// |  3|2020-04-16 03:31:10|   3|[2020-04-16, 03:3...|2020-04-19 03:31:10|
// |  6|2020-03-01 14:31:10|  30|[2020-03-01, 14:3...|2020-03-31 14:31:10|
// +---+-------------------+----+--------------------+-------------------+

Note that validation of the startDt string (e.g. via a Regex timestamp pattern matching) could be added, if necessary.

Mysql add 1 day to timestamp

If the eventstart stores a unix_timestamp (you must have defined it when you created the table) this means it the values should be in seconds. A day is 86400 seconds; so you should add this to the values in eventstart field. Consequently the update would be

UPDATE myTable SET eventstart = (eventstart + 86400)

I hope this helps.

How can i add days to a Hive timestamp without loosing hours, minutes and seconds

1. date_add truncates
2. Unnecessary unix_timestamp+from_unixtime conversion

Convert to timestamp, add interval:

SELECT timestamp("2021-01-15 09:34:21") + interval '2' day;

Result:

2021-01-17 09:34:21.0

Timestamp is displayed with zero millisecond part, it is default timestamp representation.
If you want it as string without milliseconds, format using date_format or simply take substr()

SELECT date_format(timestamp("2021-01-15 09:34:21") + interval '2' day,'yyyy-MM-dd HH:mm:ss')

Result:

2021-01-17 09:34:21

And the same using substr:

SELECT substr(timestamp("2021-01-15 09:34:21") + interval '2' day,1,19)

If you need to calculate interval dynamically and your Hive version does not support it, see this example:

with mytable as (
select timestamp("2021-01-15 09:34:21") ts, 2 d
)
SELECT from_unixtime(unix_timestamp(ts) + (d*24*60*60))
from mytable

Result:

2021-01-17 09:34:21

---

Related Topics