How to Add Months to a Current_Timestamp in Sql

How do I add months to a CURRENT_TIMESTAMP in SQL?

This works perfectly fine

SELECT DATEADD(month,1,CURRENT_TIMESTAMP)

From DATEADD (Transact-SQL)

date

Is an expression that can be
resolved to a time, date,
smalldatetime, datetime, datetime2, or
datetimeoffset value.

Adding 6 months to a CURRENT_TIMESTAMP in mysql

I don't think MySQL supports expressions on date/time for default values.

More recent versions support generated columns. This allows you to do:

CREATE TABLE IF NOT EXISTS promos
(
    createdAt datetime default CURRENT_TIMESTAMP,
    expiryDateOfReward DATETIME generated always as (createdAt + interval 6 month)
);

Also note that the syntax for adding six months is suitable for MySQL.

Add 1 month to date and also display correct time from timestamp

If you want to do algebra on a timestamp, then you should start with a literal timestamp, not a date:

SELECT
    DATEADD(month, 1, '2021-08-12 18:37:19') AS date_interval;

The problem with your current query is that the following is a date literal:

date '2021-08-12 18:37:19'

Hence, the time component of your timestamp will be "zeroed" out to midnight before you even add one month.

Oracle - Add days/months to a TIMESTAMP WITH TIME ZONE field

When you do DATED = DATED + 2, then DATED is implicitly converted to a DATE value (which does not have any time zone information) and then converted back to TIMESTAMP WITH TIME ZONE value using your current session time zone settings.

Try

UPDATE MY_TABLE SET DATED = DATED + INTERVAL '2' DAY WHERE ID = 1165;

or

UPDATE MY_TABLE SET DATED = DATED + 2 * INTERVAL '1' DAY WHERE ID = 1165;

or

UPDATE MY_TABLE SET DATED = DATED + NUMTODSINTERVAL(2, 'day') WHERE ID = 1165;

In order to add 2 months you can use

UPDATE MY_TABLE SET DATED = DATED + INTERVAL '2' MONTH WHERE ID = 1165;

resp. NUMTOYMINTERVAL(2, 'MONTH'), for example.

add 1 month from current date using java.sql.Date

The problem with your solution is that you are not doing the calculations in long.

You have to replace 31*24*60*60*1000 with 31l*24l*60l*60l*1000l.

Here is the corrected code snippet:

public static void main (String[] args)
{
    Date kini = new Date();
    java.sql.Date jadwalPengobatan = new java.sql.Date(kini.getTime() + 31l*24l*60l*60l*1000l);
    System.out.println(jadwalPengobatan);
}

Please note that this will only fix your calculation where you are adding 31 days to the current date. If you are looking to add a month which can be of 30, 28 or 29 days also then perhaps you should make use of Calendar.

Today's Date:

2016-02-27

Output:

2016-03-29

Add time to CURRENT_TIMESTAMP

For this problem it's best if you change the server's timezone.

Also, a better way is to set the server's timezone to UTC (or GMT-0) and then translate all dates on the UI layer. That way you can have a global application which will be correctly displaying the dates to all users (think gmail). For this you'll of course need a timezone preference for each user.

If you cannot permanently change the timezone on the server (because you are on a shared hosting) then you'll need to be setting the timezone every time you connect to the database using the following query:

SET time_zone = '-8:00';

Finally, to do time arithmetic you can try:

addtime(current_timestamp, '15:00')

How to add specific no. of months to a date in UNIX Timestamp format?

MySql supports the use of INTERVAL for this type of operation

INSERT INTO table SET newdate = unix_timestamp(now() + INTERVAL 5 month);

Here is an example of it in action

mysql> select unix_timestamp(),unix_timestamp(now() + interval 5 month);
+------------------+------------------------------------------+
| unix_timestamp() | unix_timestamp(now() + interval 5 month) |
+------------------+------------------------------------------+
|       1392274422 |                               1405230822 |
+------------------+------------------------------------------+
1 row in set (0.00 sec)

---

Related Topics