Difference Between Timestamp Stored and Current Date

Difference between timestamp stored and current date

Swift 3. If you are using Swift 2, use NSDate

//the first button press, this is a double but assume it's a timeInterval
let firstPress = 498898978.928 

let date = Date()

let secondPress = date.timeIntervalSinceReferenceDate //the second button press

let diffInSeconds = secondPress - firstPress //total time difference in seconds

let hours = diffInSeconds/60/60 //hours=diff in seconds / 60 sec per min / 60 min per hour

if hours < 48 {
     print("less then 48 hours difference")
} else {
     print("greater/equal to 48 hours difference")
}

Finding difference between current time and timestamp field

If you store the time with MySQL TIMESTAMP data type, then the best would be to use a MySQL function that operates on TIMESTAMP data. TIMESTAMPDIFF() would be quite suitable, as it also allows to define unit for the result.

SELECT TIMESTAMPDIFF(MINUTE, `my_timestamp_column`, NOW()) FROM `my_table`

will return number (integer) of minutes that have passed since my_timestamp_column value. Combine it with either a PHP cron script or simply with every request on your page to achieve the goal:

DELETE FROM `my_table`
WHERE TIMESTAMPDIFF(MINUTE, `my_timestamp_column`, NOW()) > 15

Additionally, you may want to read the manual's Date and Time Functions section for some other solutions.

Difference between current date and date stored in database not working

You can convert output of GETDATE() to unix timestamp by simple statement:

SELECT GETDATE(), DATEDIFF(second, '1970-01-01', GETDATE())

You can check the result with another source of timestamp (for example some PHP function). The reverse is a little bit harder.

UNIX_TIMESTAMP in SQL Server

How can I convert bigint (UNIX timestamp) to datetime in SQL Server?

Why do you use unix timestamp in SQL server? It does not have support for that. Funtions DATEDIFF, DATEADD etc. works only with real dates, not with unix timestamp seconds.

How to calculate difference between saved timestamp and current time

When you fetch the document, 'timestamp' field would be of type Timestamp and you can use seconds property and current timestamp to calculate the difference as shown below:

const snap = await getDoc(docRef);
const timeDiff = Date.now() - snap.data()?.time.seconds * 1000;

console.log(`Time Difference: ${timeDiff} ms`)

What difference between the DATE, TIME, DATETIME, and TIMESTAMP Types

DATE: It is used for values with a date part but no time part. MySQL retrieves and displays DATE values in YYYY-MM-DD format. The supported range is 1000-01-01 to 9999-12-31.

DATETIME: It is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in YYYY-MM-DD HH:MM:SS format. The supported range is 1000-01-01 00:00:00 to 9999-12-31 23:59:59.

TIMESTAMP: It is also used for values that contain both date and time parts, and includes the time zone. TIMESTAMP has a range of 1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTC.

TIME: Its values are in HH:MM:SS format (or HHH:MM:SS format for large hours values). TIME values may range from -838:59:59 to 838:59:59. The hours part may be so large because the TIME type can be used not only to represent a time of day (which must be less than 24 hours), but also elapsed time or a time interval between two events (which may be much greater than 24 hours, or even negative).

Should I use the datetime or timestamp data type in MySQL?

Timestamps in MySQL are generally used to track changes to records, and are often updated every time the record is changed. If you want to store a specific value you should use a datetime field.

If you meant that you want to decide between using a UNIX timestamp or a native MySQL datetime field, go with the native DATETIME format. You can do calculations within MySQL that way
("SELECT DATE_ADD(my_datetime, INTERVAL 1 DAY)") and it is simple to change the format of the value to a UNIX timestamp ("SELECT UNIX_TIMESTAMP(my_datetime)") when you query the record if you want to operate on it with PHP.

How to get time difference between current date time and column value in minutes

You can take advantage of the MySQL function TIMESTAMPDIFF to calculate it for you:

SELECT id,
       created_on
  FROM user
 WHERE TIMESTAMPDIFF(MINUTE, created_on, NOW()) > 30

Live DEMO.

Difference between UNIX_TIMESTAMP and NOW() in MySQL

The function NOW() generates a formatted date-time string, determined by the time zone of your MySQL server.

However, it would be better to store times using UNIX_TIMESTAMP(), which is expressed in GMT. Doing so makes it easier to format it according to the country of a visitor (e.g. using JavaScript).

If you still want to use DATETIME columns, you can store times using UTC_TIMESTAMP() (it formats a date like NOW() but expresses it in UTC); it should more or less work the same in all other aspects.

Different CURRENT_TIMESTAMP and SYSDATE in oracle

CURRENT_DATE and CURRENT_TIMESTAMP return the current date and time in the session time zone.

SYSDATE and SYSTIMESTAMP return the system date and time - that is, of the system on which the database resides.

If your client session isn't in the same timezone as the server the database is on (or says it isn't anyway, via your NLS settings), mixing the SYS* and CURRENT_* functions will return different values. They are all correct, they just represent different things. It looks like your server is (or thinks it is) in a +4:00 timezone, while your client session is in a +4:30 timezone.

You might also see small differences in the time if the clocks aren't synchronised, which doesn't seem to be an issue here.

---

Related Topics