Apa perbedaan antara cap waktu unix dan cap waktu mysql?

Di tabel travel, ada tiga kolom. id, departure, dan arrival. Anda ingin menghitung selisih antara arrival dan departure

Nội dung chính Show

Apa itu cap waktu Unix di MySQL?
Apa perbedaan antara cap waktu dan cap waktu Unix?
Apa perbedaan antara waktu dan stempel waktu di MySQL?
Bagaimana cara menghitung perbedaan antara dua cap waktu Unix di MySQL?

Tabel travel_ terlihat seperti ini

iddeparturearrival12018-03-25 12. 00. 002018-04-05 07. 30. 0022019-09-12 15. 50. 002019-10-23 10. 30. 3032018-07-14 16. 15. 002018-07-14 20. 40. 3042018-01-05 08. 35. 002019-01-08 14. 00. 00

Solusi 1 (perbedaan hari, jam, menit, atau detik)

SELECT
  id,
  departure,
  arrival,
  TIMESTAMPDIFF(SECOND, departure, arrival) AS difference
FROM travel;

Hasilnya adalah

iddeparturearrivaldifference12018-03-25 12. 00. 002018-04-05 07. 30. 0093420022019-09-12 15. 50. 002019-10-23 10. 30. 30352323032018-07-14 16. 15. 002018-07-14 20. 40. 301593042018-01-05 08. 35. 002019-01-08 14. 00. 0031814700

Diskusi

Untuk menghitung perbedaan antara cap waktu di MySQL, gunakan fungsi. Argumen satuan dapat berupa

WITH difference_in_seconds AS (
  SELECT
    id,
    departure,
    arrival,
    TIMESTAMPDIFF(SECOND, departure, arrival) AS seconds
  FROM travel
),

differences AS (
  SELECT
    id,
    departure,
    arrival,
    seconds,
    MOD(seconds, 60) AS seconds_part,
    MOD(seconds, 3600) AS minutes_part,
    MOD(seconds, 3600 * 24) AS hours_part
  FROM difference_in_seconds
)

SELECT
  id,
  departure,
  arrival,
  CONCAT(
    FLOOR(seconds / 3600 / 24), ' days ',
    FLOOR(hours_part / 3600), ' hours ',
    FLOOR(minutes_part / 60), ' minutes ',
    seconds_part, ' seconds'
  ) AS difference
FROM differences;

WITH difference_in_seconds AS (
  SELECT
    id,
    departure,
    arrival,
    TIMESTAMPDIFF(SECOND, departure, arrival) AS seconds
  FROM travel
),

differences AS (
  SELECT
    id,
    departure,
    arrival,
    seconds,
    MOD(seconds, 60) AS seconds_part,
    MOD(seconds, 3600) AS minutes_part,
    MOD(seconds, 3600 * 24) AS hours_part
  FROM difference_in_seconds
)

SELECT
  id,
  departure,
  arrival,
  CONCAT(
    FLOOR(seconds / 3600 / 24), ' days ',
    FLOOR(hours_part / 3600), ' hours ',
    FLOOR(minutes_part / 60), ' minutes ',
    seconds_part, ' seconds'
  ) AS difference
FROM differences;

WITH difference_in_seconds AS (
  SELECT
    id,
    departure,
    arrival,
    TIMESTAMPDIFF(SECOND, departure, arrival) AS seconds
  FROM travel
),

differences AS (
  SELECT
    id,
    departure,
    arrival,
    seconds,
    MOD(seconds, 60) AS seconds_part,
    MOD(seconds, 3600) AS minutes_part,
    MOD(seconds, 3600 * 24) AS hours_part
  FROM difference_in_seconds
)

SELECT
  id,
  departure,
  arrival,
  CONCAT(
    FLOOR(seconds / 3600 / 24), ' days ',
    FLOOR(hours_part / 3600), ' hours ',
    FLOOR(minutes_part / 60), ' minutes ',
    seconds_part, ' seconds'
  ) AS difference
FROM differences;

WITH difference_in_seconds AS (
  SELECT
    id,
    departure,
    arrival,
    TIMESTAMPDIFF(SECOND, departure, arrival) AS seconds
  FROM travel
),

differences AS (
  SELECT
    id,
    departure,
    arrival,
    seconds,
    MOD(seconds, 60) AS seconds_part,
    MOD(seconds, 3600) AS minutes_part,
    MOD(seconds, 3600 * 24) AS hours_part
  FROM difference_in_seconds
)

SELECT
  id,
  departure,
  arrival,
  CONCAT(
    FLOOR(seconds / 3600 / 24), ' days ',
    FLOOR(hours_part / 3600), ' hours ',
    FLOOR(minutes_part / 60), ' minutes ',
    seconds_part, ' seconds'
  ) AS difference
FROM differences;

WITH difference_in_seconds AS (
  SELECT
    id,
    departure,
    arrival,
    TIMESTAMPDIFF(SECOND, departure, arrival) AS seconds
  FROM travel
),

differences AS (
  SELECT
    id,
    departure,
    arrival,
    seconds,
    MOD(seconds, 60) AS seconds_part,
    MOD(seconds, 3600) AS minutes_part,
    MOD(seconds, 3600 * 24) AS hours_part
  FROM difference_in_seconds
)

SELECT
  id,
  departure,
  arrival,
  CONCAT(
    FLOOR(seconds / 3600 / 24), ' days ',
    FLOOR(hours_part / 3600), ' hours ',
    FLOOR(minutes_part / 60), ' minutes ',
    seconds_part, ' seconds'
  ) AS difference
FROM differences;

WITH difference_in_seconds AS (
  SELECT
    id,
    departure,
    arrival,
    TIMESTAMPDIFF(SECOND, departure, arrival) AS seconds
  FROM travel
),

differences AS (
  SELECT
    id,
    departure,
    arrival,
    seconds,
    MOD(seconds, 60) AS seconds_part,
    MOD(seconds, 3600) AS minutes_part,
    MOD(seconds, 3600 * 24) AS hours_part
  FROM difference_in_seconds
)

SELECT
  id,
  departure,
  arrival,
  CONCAT(
    FLOOR(seconds / 3600 / 24), ' days ',
    FLOOR(hours_part / 3600), ' hours ',
    FLOOR(minutes_part / 60), ' minutes ',
    seconds_part, ' seconds'
  ) AS difference
FROM differences;

WITH difference_in_seconds AS (
  SELECT
    id,
    departure,
    arrival,
    TIMESTAMPDIFF(SECOND, departure, arrival) AS seconds
  FROM travel
),

differences AS (
  SELECT
    id,
    departure,
    arrival,
    seconds,
    MOD(seconds, 60) AS seconds_part,
    MOD(seconds, 3600) AS minutes_part,
    MOD(seconds, 3600 * 24) AS hours_part
  FROM difference_in_seconds
)

SELECT
  id,
  departure,
  arrival,
  CONCAT(
    FLOOR(seconds / 3600 / 24), ' days ',
    FLOOR(hours_part / 3600), ' hours ',
    FLOOR(minutes_part / 60), ' minutes ',
    seconds_part, ' seconds'
  ) AS difference
FROM differences;

WITH difference_in_seconds AS (
  SELECT
    id,
    departure,
    arrival,
    TIMESTAMPDIFF(SECOND, departure, arrival) AS seconds
  FROM travel
),

differences AS (
  SELECT
    id,
    departure,
    arrival,
    seconds,
    MOD(seconds, 60) AS seconds_part,
    MOD(seconds, 3600) AS minutes_part,
    MOD(seconds, 3600 * 24) AS hours_part
  FROM difference_in_seconds
)

SELECT
  id,
  departure,
  arrival,
  CONCAT(
    FLOOR(seconds / 3600 / 24), ' days ',
    FLOOR(hours_part / 3600), ' hours ',
    FLOOR(minutes_part / 60), ' minutes ',
    seconds_part, ' seconds'
  ) AS difference
FROM differences;

9, atau

CONCAT(
    FLOOR(seconds / 3600 / 24), ' days ',
    FLOOR(hours_part / 3600), ' hours ',
    FLOOR(minutes_part / 60), ' minutes ',
    seconds_part, ' seconds'
  ) AS difference

0. Untuk mendapatkan selisih detik seperti yang telah kita lakukan di sini, pilih

WITH difference_in_seconds AS (
  SELECT
    id,
    departure,
    arrival,
    TIMESTAMPDIFF(SECOND, departure, arrival) AS seconds
  FROM travel
),

differences AS (
  SELECT
    id,
    departure,
    arrival,
    seconds,
    MOD(seconds, 60) AS seconds_part,
    MOD(seconds, 3600) AS minutes_part,
    MOD(seconds, 3600 * 24) AS hours_part
  FROM difference_in_seconds
)

SELECT
  id,
  departure,
  arrival,
  CONCAT(
    FLOOR(seconds / 3600 / 24), ' days ',
    FLOOR(hours_part / 3600), ' hours ',
    FLOOR(minutes_part / 60), ' minutes ',
    seconds_part, ' seconds'
  ) AS difference
FROM differences;

3. Untuk mendapatkan perbedaan dalam menit, pilih

WITH difference_in_seconds AS (
  SELECT
    id,
    departure,
    arrival,
    TIMESTAMPDIFF(SECOND, departure, arrival) AS seconds
  FROM travel
),

differences AS (
  SELECT
    id,
    departure,
    arrival,
    seconds,
    MOD(seconds, 60) AS seconds_part,
    MOD(seconds, 3600) AS minutes_part,
    MOD(seconds, 3600 * 24) AS hours_part
  FROM difference_in_seconds
)

SELECT
  id,
  departure,
  arrival,
  CONCAT(
    FLOOR(seconds / 3600 / 24), ' days ',
    FLOOR(hours_part / 3600), ' hours ',
    FLOOR(minutes_part / 60), ' minutes ',
    seconds_part, ' seconds'
  ) AS difference
FROM differences;

4; . Argumen akhir dan awal masing-masing adalah stempel waktu akhir dan stempel waktu awal (di sini, departure dan arrival,

CONCAT(
    FLOOR(seconds / 3600 / 24), ' days ',
    FLOOR(hours_part / 3600), ' hours ',
    FLOOR(minutes_part / 60), ' minutes ',
    seconds_part, ' seconds'
  ) AS difference

Solusi 2 (perbedaan hari, jam, menit, dan detik)

WITH difference_in_seconds AS (
  SELECT
    id,
    departure,
    arrival,
    TIMESTAMPDIFF(SECOND, departure, arrival) AS seconds
  FROM travel
),

differences AS (
  SELECT
    id,
    departure,
    arrival,
    seconds,
    MOD(seconds, 60) AS seconds_part,
    MOD(seconds, 3600) AS minutes_part,
    MOD(seconds, 3600 * 24) AS hours_part
  FROM difference_in_seconds
)

SELECT
  id,
  departure,
  arrival,
  CONCAT(
    FLOOR(seconds / 3600 / 24), ' days ',
    FLOOR(hours_part / 3600), ' hours ',
    FLOOR(minutes_part / 60), ' minutes ',
    seconds_part, ' seconds'
  ) AS difference
FROM differences;

Hasilnya adalah

iddeparturearrivaldifference12018-03-25 12. 00. 002018-04-05 07. 30. 0010 hari 19 jam 30 menit 0 detik22019-09-12 15. 50. 002019-10-23 10. 30. 3040 hari 18 jam 40 menit 30 detik32018-07-14 16. 15. 002018-07-14 20. 40. 300 hari 4 jam 25 menit 30 detik42018-01-05 08. 35. 002019-01-08 14. 00. 00368 hari 5 jam 25 menit 0 detik

Diskusi

Pertama, hitung perbedaan antara stempel waktu dalam detik, menggunakan fungsi

CONCAT(
    FLOOR(seconds / 3600 / 24), ' days ',
    FLOOR(hours_part / 3600), ' hours ',
    FLOOR(minutes_part / 60), ' minutes ',
    seconds_part, ' seconds'
  ) AS difference

7 (CTE pertama, bernama

CONCAT(
    FLOOR(seconds / 3600 / 24), ' days ',
    FLOOR(hours_part / 3600), ' hours ',
    FLOOR(minutes_part / 60), ' minutes ',
    seconds_part, ' seconds'
  ) AS difference

8), seperti pada Solusi 1. Hitung berapa detik kelebihan menit penuh (

CONCAT(
    FLOOR(seconds / 3600 / 24), ' days ',
    FLOOR(hours_part / 3600), ' hours ',
    FLOOR(minutes_part / 60), ' minutes ',
    seconds_part, ' seconds'
  ) AS difference

9) yang akan digunakan nanti untuk menghitung detik, berapa detik ada kelebihan jam penuh (

FLOOR(seconds / 3600 / 24) AS days,
FLOOR(hours_part / 3600) AS hours,
FLOOR(minutes_part / 60) AS minutes,
seconds_part AS seconds

0) yang akan digunakan nanti untuk menghitung menit, dan berapa detik lagi

Untuk melakukannya, gunakan fungsi

FLOOR(seconds / 3600 / 24) AS days,
FLOOR(hours_part / 3600) AS hours,
FLOOR(minutes_part / 60) AS minutes,
seconds_part AS seconds

2. Misalnya, satu jam memiliki 3600 detik, jadi untuk menemukan berapa detik yang ada di

FLOOR(seconds / 3600 / 24) AS days,
FLOOR(hours_part / 3600) AS hours,
FLOOR(minutes_part / 60) AS minutes,
seconds_part AS seconds

0, temukan sisa dari pembagian dengan 3600 seperti ini

FLOOR(seconds / 3600 / 24) AS days,
FLOOR(hours_part / 3600) AS hours,
FLOOR(minutes_part / 60) AS minutes,
seconds_part AS seconds

Demikian pula, ada

FLOOR(seconds / 3600 / 24) AS days,
FLOOR(hours_part / 3600) AS hours,
FLOOR(minutes_part / 60) AS minutes,
seconds_part AS seconds

_5 detik dalam sehari, jadi untuk menghitung berapa detik dalam

FLOOR(seconds / 3600 / 24) AS days,
FLOOR(hours_part / 3600) AS hours,
FLOOR(minutes_part / 60) AS minutes,
seconds_part AS seconds

1, tulis

FLOOR(seconds / 3600 / 24) AS days,
FLOOR(hours_part / 3600) AS hours,
FLOOR(minutes_part / 60) AS minutes,
seconds_part AS seconds

Setelah sisa ini dihitung (dalam CTE kedua, bernama

FLOOR(seconds / 3600 / 24) AS days,
FLOOR(hours_part / 3600) AS hours,
FLOOR(minutes_part / 60) AS minutes,
seconds_part AS seconds

8), Anda akhirnya bisa mendapatkan perbedaan dalam hari, jam, menit, dan detik. Untuk mendapatkan jumlah detik, menit, jam, dan hari, bagilah jumlah detik sisanya dengan jumlah detik yang sesuai dalam hari, jam, atau menit. Misalnya, untuk mengetahui berapa menit yang harus ditampilkan, ambil

FLOOR(seconds / 3600 / 24) AS days,
FLOOR(hours_part / 3600) AS hours,
FLOOR(minutes_part / 60) AS minutes,
seconds_part AS seconds

0 dan bagi dengan 60, karena ada 60 menit dalam satu jam. Anda hanya memerlukan bagian bilangan bulat dari ini (mis. e. , tanpa bagian desimal), jadi gunakan fungsi FLOOR() seperti ini

travel0

Terakhir, Anda hanya perlu menampilkan dalam satu string apa yang telah Anda hitung. Untuk melakukannya, gunakan fungsi travel_1 di kueri luar

CONCAT(
    FLOOR(seconds / 3600 / 24), ' days ',
    FLOOR(hours_part / 3600), ' hours ',
    FLOOR(minutes_part / 60), ' minutes ',
    seconds_part, ' seconds'
  ) AS difference

Solusi yang disajikan di sini mengembalikan kolom terakhir sebagai teks. Anda dapat dengan mudah memodifikasi solusi ini untuk menampilkannya dalam beberapa format lain. Anda juga dapat menampilkan angka dalam kolom terpisah, seperti ini

Apa itu cap waktu Unix di MySQL?

UNIX_TIMESTAMP() fungsi di MySQL . 00. 00'UTC the number of seconds that have passed since '1970-01-01 00:00:00'UTC . Bahkan jika Anda melewati tanggal/waktu saat ini atau tanggal/waktu lain yang ditentukan, fungsi akan mengembalikan stempel waktu Unix berdasarkan itu.

Apa perbedaan antara cap waktu dan cap waktu Unix?

Di komputer, stempel waktu menunjukkan waktu peristiwa pertama kali direkam oleh komputer. Itu tidak harus fokus pada waktu peristiwa itu terjadi. Apa itu Stempel Waktu Unix? . The Unix timestamp refers to the number of seconds that have been spent since January 1, 1970, without the inclusion of leap seconds.

Apa perbedaan antara waktu dan stempel waktu di MySQL?

Tipe DATETIME digunakan untuk nilai yang berisi bagian tanggal dan waktu. MySQL mengambil dan menampilkan nilai DATETIME di ' YYYY-MM-DD hh. mm. format ss. Kisaran yang didukung adalah '1000-01-01 00. 00. 00' hingga '9999-12-31 23. 59. 59'. Tipe data TIMESTAMP digunakan untuk nilai yang berisi bagian tanggal dan waktu .

Bagaimana cara menghitung perbedaan antara dua cap waktu Unix di MySQL?

Untuk menghitung perbedaan antara cap waktu di MySQL, gunakan fungsi TIMESTAMPDIFF(unit, start, end) . Argumen satuan dapat berupa MICROSECOND , SECOND , MINUTE , HOUR , DAY , WEEK , MONTH , QUARTER , atau YEAR. Untuk mendapatkan selisih detik seperti yang telah kita lakukan di sini, pilih SECOND.