mysql date format timestamp functions with examples
Αυτό το περιεκτικό σεμινάριο παρέχει σύνταξη και παραδείγματα λειτουργιών μορφής ημερομηνίας MySQL και χρονικής σήμανσης όπως τρέχουσα ημερομηνία, ημερομηνία προσθήκης, προσθήκη ώρας κ.λπ .:
Σε αυτό το σεμινάριο, θα μάθουμε σε βάθος για τους τύπους δεδομένων Ημερομηνία και Ώρα MySQL και τις διάφορες λειτουργίες που παρέχονται από τη MySQL για το χειρισμό των τιμών ημερομηνίας και ώρας.
Η MySQL παρέχει ένα μεγάλο σύνολο λειτουργιών / βοηθητικών προγραμμάτων για την εκτέλεση διαφορετικών λειτουργιών, όπως προσθήκη και αφαίρεση ημερομηνιών, εύρεση της διαφοράς μεταξύ 2 ημερομηνιών και εξαγωγή συγκεκριμένων τμημάτων δεδομένης τιμής ημερομηνίας-ώρας.
Τι θα μάθετε:
- Μορφή ημερομηνίας MySQL και συναρτήσεις ώρας
- συμπέρασμα
Μορφή ημερομηνίας MySQL και συναρτήσεις ώρας
Αυτές οι συναρτήσεις είναι πολύ σημαντικές όταν απαιτούνται οποιεσδήποτε λειτουργίες σε τιμές στήλης ημερομηνίας ώρας στους πίνακες MySQL.
Προαπαιτούμενα
Για την εκτέλεση των παραδειγμάτων που χρησιμοποιούνται στο σεμινάριο, θα χρειαστείτε μια τοπική εγκατάσταση της εγκατάστασης MySQL.
= >> Για την εγκατάσταση της MySQL ανατρέξτε στο σεμινάριό μας εδώ.
Τύποι δεδομένων ΗΜΕΡΟΜΗΝΙΑΣ ΚΑΙ ΩΡΑ
Οι τύποι δεδομένων περιλαμβάνουν YEAR, DATE, TIME, DATETIME και TIMESTAMP.
Η MySQL αποθηκεύει τιμές ημερομηνίας και ώρας χρησιμοποιώντας 5 διαφορετικούς τύπους δεδομένων, δηλαδή - ΕΤΟΣ, ΗΜΕΡΟΜΗΝΙΑ, ΧΡΟΝΟΣ, ΗΜΕΡΟΜΗΝΙΑ και ΧΡΟΝΟΣ ΤΥΠΟΥ. Ας προσπαθήσουμε να τα κατανοήσουμε εν συντομία στον παρακάτω πίνακα, προτού εμβαθύνουμε βαθύτερα σε ΜΟΡΦΕΣ ΗΜΕΡΟΜΗΝΙΑΣ και ΛΕΙΤΟΥΡΓΙΕΣ ΗΜΕΡΟΜΗΝΙΑΣ που παρέχονται από τη MySQL
| Τύπος | Σύνταξη | Εύρος | Μνήμη | Μορφή |
|---|---|---|---|---|
| % Η | Ώρα - 00 - 23 | |||
| ΕΤΟΣ | Χρησιμοποιείται για την αποθήκευση μόνο του έτους colName ΕΤΟΣ | 1901 - 2155 | 1 | «ΕΕΕΕ» |
| ΗΜΕΡΟΜΗΝΙΑ | Χρησιμοποιήθηκε για την αποθήκευση μόνο του τμήματος DATE. colName ΗΜΕΡΟΜΗΝΙΑ | «1000-01-01» έως «9999-12-31» | 3 | «ΕΕΕΕ-ΜΜ-ΗΗ» |
| ΧΡΟΝΟΣ | Χρησιμοποιείται για την αποθήκευση μόνο του τμήματος TIME. colName ΧΡΟΝΟΣ colName ΩΡΑ (ν) n -> ακρίβεια ή κλασματικό στοιχείο | ‘00: 00: 00 ’έως ‘23: 59: 59’ | 3 + κλασματική αποθήκευση | «Hh: mm: ss. (Κλάσμα)» |
| ΗΜΕΡΟΜΗΝΙΑ ΩΡΑ | Χρησιμοποιείται για την αποθήκευση τόσο DATE όσο και TIME colName ΗΜΕΡΟΜΗΝΙΑ ΩΡΑ colName ΗΜΕΡΟΜΗΝΙΑ (n) n -> ακρίβεια ή κλασματικό στοιχείο | '1000-01-01 00: 00: 00.000000' έως '9999-12-31 23: 59: 59.999999' | 5 + κλασματική αποθήκευση | «YYYY-MM-DD hh: mm: ss. (Κλάσμα)» |
| TIMESTAMP | Χρησιμοποιείται για την αποθήκευση τόσο DATE όσο και TIME colName TIMESTAMP colName TIMESTAMP (n) n -> ακρίβεια ή κλασματικό στοιχείο | '1970-01-01 00:00:01' UTC έως '2038-01-19 03:14:07' UTC. | 4 + κλασματική αποθήκευση | «YYYY-MM-DD hh: mm: ss. (Κλάσμα)» |
Τώρα θα κατανοήσουμε τις απαιτήσεις μνήμης για κλασματική αποθήκευση. Για τύπους δεδομένων TIME, DATETIME και TIMESTAMP ανάλογα με το καθορισμένο κλασματικό στοιχείο, η μνήμη που καταναλώνεται θα είναι διαφορετική.
| Αριθμός κλασματικών ψηφίων | Μνήμη (σε byte) |
|---|---|
| 0 | 0 |
| 1-2 | 1 |
| 3-4 | δύο |
| 5-6 | 3 |
Χρησιμοποιώντας τον παραπάνω πίνακα, για παράδειγμα, εάν έχουμε έναν τύπο στήλης ως - TIME (5).
Στη συνέχεια, η συνολική μνήμη που χρησιμοποιήθηκε θα ήταν => μνήμη που χρησιμοποιείται από τον τύπο δεδομένων TIME + μνήμη που χρησιμοποιείται από 5 κλασματικό στοιχείο => 3 + 3 => 6 byte
Ας απεικονίσουμε όλους αυτούς τους τύπους δεδομένων ως μέρος ενός μεμονωμένου πίνακα και να δούμε τις τιμές που έχουν αντιστοιχιστεί σε καθέναν από τους τύπους.
CREATE TABLE datetime_illustration(year_col YEAR, date_col DATE, time_col TIME, time_col_fractional TIME(2), datetime_col DATETIME, datetime_col_fractional DATETIME(6), timestamp_col TIMESTAMP, timestamp_col_fractional TIMESTAMP(6)) Εισαγάγετε μια μεμονωμένη σειρά στον πίνακα χρησιμοποιώντας τη συνάρτηση ‘now ()’, που είναι η τρέχουσα χρονική σήμανση. Παρατηρήστε πώς η χρήση αυτής της λειτουργίας θα είχε ως αποτέλεσμα την περικοπή / εισαγωγή μόνο του απαιτούμενου μέρους της τρέχουσας ημερομηνίας και ώρας, ανάλογα με τον τύπο δεδομένων της στήλης.
insert into datetime_illustration values (now(), now(), now(), now(), now(), now(), now(), now()); select * from datetime_illustration Τώρα, ας δούμε τα περιεχόμενα του πίνακα για να αναλύσουμε τα δεδομένα σε διαφορετικές στήλες.
select * from datetime_illustration 
Έτσι, συνοψίζουμε τα παραπάνω δεδομένα ως,
- Όλες οι στήλες έλαβαν εκχωρημένες τιμές ανά τύπο δεδομένων, για παράδειγμα, για την πρώτη στήλη - ΕΤΟΣ - αν και εισαγάγαμε χρησιμοποιώντας τη συνάρτηση 'now ()' που είναι η τρέχουσα χρονική σήμανση, η τιμή που εισήχθη είναι μόνο το έτος μέρος της τρέχουσας ημερομηνίας ημερομηνίας - δηλαδή 2020.
- η συνάρτηση now () επιστρέφει την τρέχουσα ώρα σε μορφή - ηη: mm: εεεε ωω: mm: ss
- Παρατηρήστε το κλασματικό μέρος στα ονόματα των στηλών με το «_fractional». Ανάλογα με τον αριθμό των κλασματικών ψηφίων που καθορίζονται, οι κλασματικές τιμές καταγράφονται και εμφανίζονται.
Αυτόματη προετοιμασία και ενημέρωση πεδίων DATETIME και TIMESTAMP
Στα σενάρια πραγματικής ζωής - τις περισσότερες φορές, η προετοιμασία και οι ενημερώσεις DATETIME έχουν ρυθμιστεί ώστε να είναι αυτόματες.
Αυτό σημαίνει ότι γενικά οι πίνακες έχουν 2 στήλες όπως - δημιουργήθηκε_ ώρα και ενημέρωση_ ώρα .
Η στήλη create_time έχει διαμορφωθεί ώστε να λαμβάνει την τιμή του τρέχοντος χρόνου συστήματος κάθε φορά που εισάγεται η σειρά και παρόμοια ενημέρωση_ ώρα έχει ρυθμιστεί ώστε να λαμβάνει τον τρέχοντα χρόνο συστήματος κάθε φορά που υπάρχουν ενημερώσεις σε μια συγκεκριμένη σειρά του πίνακα.
Παρακαλούμε να σημειώσετε ότι, κατά τη διάρκεια της εισαγωγής της σειράς, τόσο το create_time όσο και το update_time θα έχουν την ίδια τιμή. Όλες οι επόμενες ενημερώσεις στη σειρά θα έχουν ως αποτέλεσμα την ενημέρωση του πεδίου ενημέρωση_ ώρα με την τρέχουσα ώρα συστήματος.
Ας προσπαθήσουμε να δημιουργήσουμε έναν τέτοιο πίνακα που έχει αυτόματη προετοιμασία καθώς και ενημέρωση.
Θα δημιουργήσουμε έναν πίνακα με το όνομα τιμή μετοχής που έχει τα πεδία σύμβολο μετοχής, τιμής και ημερομηνίας - create_on και updated_on.
create table stock_price(symbol varchar(100), price decimal(10,2), created_on datetime default current_timestamp, updated_on datetime default current_timestamp on update current_timestamp) Εισαγάγετε ορισμένα δεδομένα στον πίνακα:
insert into stock_price(symbol,price) values('AMZN','3000.10');Ας δούμε το αποτέλεσμα (σημειώστε ότι μόλις εισαγάγαμε τιμές για το σύμβολο και την τιμή - οι στήλες ώρας ώρας, create_on και ενημέρωση_on πρέπει να αρχικοποιηθούν αυτόματα)
select * from stock_price 
Πρώτη αναζήτηση c ++ δέντρο
Δοκιμάστε να ενημερώσετε την τιμή της μετοχής για το απόθεμα «AMZN» όπως φαίνεται παρακάτω:
update stock_price set price='3005.15' where symbol='AMZN'Και τώρα, πάρτε ξανά τα δεδομένα για τον πίνακα. Θα πρέπει να δούμε ότι η αξία της ενημέρωσης_ ώρα αλλάζει σε οποιαδήποτε στιγμή συνέβη η ενημέρωση στην τιμή stock_price.
select * from stock_price 
Τώρα μπορείτε να παρατηρήσετε από το παραπάνω παράδειγμα ότι η τιμή της στήλης updated_on ενημερώθηκε στη χρονική σήμανση ενημέρωσης.
Λειτουργίες ημερομηνίας και ώρας MySQL
Η MySQL παρέχει πολλές λειτουργίες για στήλες με τύπους δεδομένων που σχετίζονται με ημερομηνία και ώρα.
Αυτές οι λειτουργίες χρησιμοποιούνται συνήθως για,
- Μετατρέψτε μια αποθηκευμένη ημερομηνία σε μια απαιτούμενη μορφή ενώ υποβάλλετε ερώτημα στα δεδομένα.
- Χειρισμός υπαρχόντων πεδίων ώρας ημερομηνίας, όπως προσθήκη ενός διαστήματος στην τρέχουσα τιμή της στήλης, αφαίρεση ωρών από τις τρέχουσες καθορισμένες τιμές κ.λπ.
- Εξαγωγή συγκεκριμένων πληροφοριών από τις αποθηκευμένες τιμές, για παράδειγμα, εξάγοντας μόνο το μέρος 'ημερομηνία' από την αποθηκευμένη τιμή και κάνοντας περαιτέρω φιλτράρισμα / λειτουργίες όπως ΟΜΑΔΑ ΑΠΟ με την ημερομηνία, κ.λπ.
Σε αυτήν την ενότητα, θα δούμε παραδείγματα έναντι των πιο συχνά χρησιμοποιούμενων συναρτήσεων ημερομηνίας και ώρας.
>> Ανατρέξτε εδώ για μια πλήρη λίστα τέτοιων λειτουργιών.
# 1) Λήψη τρέχουσας ημερομηνίας και ώρας
Η MySQL παρέχει πολλές εύχρηστες συναρτήσεις / σταθερές τιμές για την ανάκτηση των τρεχουσών τιμών ημερομηνίας και ώρας και χρησιμοποιούνται γενικά κατά την αποθήκευση τιμών στις στήλες ώρας δεδομένων ή ανάκτηση έναντι συγκεκριμένου εύρους από τις τρέχουσες τιμές ημερομηνίας και ώρας.
α) curdate ()
Αυτή η συνάρτηση λαμβάνει την τρέχουσα ημερομηνία. Άλλα συνώνυμα της συνάρτησης curdate () είναι - CURRENT_DATE, CURRENT_DATE (), CURRENT_DATE ( κλασματικά ψηφία )
Το παράδειγμα αυτής της συνάρτησης φαίνεται παρακάτω:
select curdate(), CURRENT_DATE(), CURRENT_DATE 
β) χρόνος παράλειψης ()
Αυτή η λειτουργία λαμβάνει την τρέχουσα ώρα. Για να αποκτήσετε ένα κλασματικό μέρος (έως 6 ψηφία) καθορίστε το κλάσμα στις παρενθέσεις ως χρονικό διάστημα (3) Αυτό θα έπαιρνε την τρέχουσα τιμή του χρόνου συστήματος έως και 3 δεκαδικά ψηφία.
Άλλα συνώνυμα της λειτουργίας curtime () είναι- CURRENT_TIME, CURRENT_TIME (), CURRENT_TIME ( κλασματικά ψηφία )
select curtime(), CURRENT_TIME(), CURRENT_TIME, curtime(6), CURRENT_TIME(6) 
γ) τώρα ()
Αυτή η λειτουργία λαμβάνει την τρέχουσα χρονική σήμανση. Καθορίστε κλασματικό μέρος στις παρενθέσεις, για παράδειγμα, τώρα (4) θα έπαιρνε την τρέχουσα χρονική σήμανση έως και 4 θέσεις δεκαδικού.
Άλλα συνώνυμα της συνάρτησης τώρα () περιλαμβάνουν CURRENT_TIMESTAMP, CURRENT_TIMESTAMP (), CURRENT_TIMESTAMP ( κλασματικά ψηφία )
select now(), CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP, now(6), CURRENT_TIMESTAMP(6) 
# 2) Προσθήκη και αφαίρεση ημερομηνιών
Η MySQL παρέχει συναρτήσεις για την προσθήκη και αφαίρεση τιμών ημερομηνίας και ώρας σε μια υπάρχουσα τιμή που είναι αποθηκευμένη στη στήλη ή για χρήση κατά την αναζήτηση δεδομένων. Θα κατανοήσουμε τις λειτουργίες με τη βοήθεια παραδειγμάτων.
Θα χρησιμοποιήσουμε τον παρακάτω πίνακα για την εκτέλεση προσθήκης ημερομηνίας-ώρας στην αφαίρεση.
create table datetime_calc(datecol DATE, timecol TIME, datetimecol DATETIME(6));Εισαγάγετε μερικές καταχωρήσεις στον παραπάνω πίνακα χρησιμοποιώντας τις δηλώσεις ΕΙΣΑΓΩΓΗΣ όπως φαίνεται παρακάτω:
insert into datetime_calc values(now(), now(), now(6)); insert into datetime_calc values(now(), now(), now(6)); Ας ρωτήσουμε τον παραπάνω πίνακα και δείτε τα αποθηκευμένα δεδομένα:
Εκτός από την ενημέρωση των στηλών σε έναν πίνακα, μπορούμε να κάνουμε άμεσα χειραγώγηση μιας ώρας δεδομένων χρησιμοποιώντας δήλωση SELECT εφαρμόζοντας οποιαδήποτε συγκεκριμένη ημερομηνία / ώρα ή τιμή χρόνου.
α) ΠΡΟΣΘΗΚΗ ()
Όπως υποδηλώνει το όνομα, αυτή η συνάρτηση χρησιμοποιείται για την προσθήκη μιας ημερομηνίας σε μια υπάρχουσα τιμή ημερομηνίας που μπορεί να είναι τύπου - DATE, DATETIME ή TIMESTAMP
Σύνταξη:
ADDDATE(inputTimeValue/fieldName, noOfDays)ADDDATE μπορεί επίσης να κληθεί με τιμή διαστήματος - η οποία θα μπορούσε να είναι DAYS, WEEK, HOUR, MINUTE, SECOND κ.λπ.
Η σύνταξη, σε αυτήν την περίπτωση, θα αλλάξει σε,
ADDDATE(inputTimeValue/fieldName, Interval intervalUnit)Ας δούμε μερικά παραδείγματα για να το κατανοήσουμε καλύτερα.
SELECT ADDDATE('2020-08-15', 31); // output '2020-09-15' Όταν μια ακέραια τιμή παρέχεται ως το δεύτερο όρισμα, αντιμετωπίζεται ως ο αριθμός των ημερών που θα προστεθούν στη δεδομένη ημερομηνία.
Μερικά ακόμη παραδείγματα με το Interval όπως - MONTH, HOUR και WEEK είναι:
SELECT ADDDATE('2020-01-02', INTERVAL 1 MONTH); //output 2020-02-02 SELECT ADDDATE('2020-01-02 12:11:11', INTERVAL 5 HOUR); //output 2020-01-02 17:11:11 SELECT ADDDATE('2020-01-02 12:11:11', INTERVAL 1 WEEK); //output 2020-01-09 12:11:11 Άλλα παραδείγματα διαστημάτων θα μπορούσαν να είναι - MINUTE, SECOND, QUARTER κ.λπ.
Για ενημέρωση μιας στήλης του υπάρχοντος πίνακα, για παράδειγμα, για τον πίνακα που δημιουργήσαμε «datetime_calc» εάν θέλουμε να ενημερώσουμε την τιμή «datecol» και να προσθέσουμε 10 ημέρες σε όλες τις ημερομηνίες στη στήλη, θα μπορούσαμε να χρησιμοποιήσουμε το ερώτημα όπως παρακάτω:
update datetime_calc set datecol=ADDDATE(datecol, 30);Τώρα, χρησιμοποιήστε SELECT * για να εμφανίσετε όλα τα δεδομένα στον πίνακα datetime_calc.
SELECT * FROM datetime_calc 
Μπορούμε να δούμε στην παραπάνω έξοδο ότι οι τιμές της στήλης δεδομένων έχουν ενημερωθεί σε 24-09-2020 από 25-08-2020 μετά την προσθήκη 30 ημερών στις υπάρχουσες τιμές.
β) ΠΡΟΣΘΗΚΗ ()
Παρόμοια με ADD_DATE, η συνάρτηση ADD_TIME μπορεί να χρησιμοποιηθεί για την προσθήκη ενός στοιχείου ώρας σε μια υπάρχουσα - στήλη / πεδίο DATETIME, TIME ή TIMESTAMP ή δεδομένη εισαγωγή.
Σύνταξη:
ADDTIME(inputTimeValue/fieldName, ‘hh:mm:ss’)Παράδειγμα:
SELECT ADDTIME('2020-01-02 12:11:11', '01:10:00'); // output 2020-01-02 13:21:11 Ας δούμε ένα παράδειγμα όπου ενημερώνουμε μια στήλη ώρας δεδομένων σε έναν πίνακα προσθέτοντας 5 ώρες σε όλες τις τιμές μέσα σε αυτήν τη στήλη. Ακολουθεί ένα δείγμα ερωτήματος:
update datetime_calc set datetimecol=ADDTIME(datetimecol, '05:00:00')Έξοδος πίνακα πριν:
// output '2020-08-25 05:53:37.851379' '2020-08-25 05:53:39.711515' Έξοδος πίνακα μετά:
// output '2020-08-25 10:53:37.851379' '2020-08-25 10:53:39.711515' γ) ΥΠΟΒΟΛΗ ()
Το SUBDATE είναι ακριβώς παρόμοιο με το ADDDATE, με τη μόνη διαφορά, το SUBDATE να αφαιρεί το δεδομένο Διάστημα / Όχι. ημερών από την τιμή στήλης / πεδίου.
Σύνταξη:
SUBDATE(inputTimeValue/fieldName, noOfDays)Ας δούμε μερικά παραδείγματα για να δείξουμε τη συνάρτηση SUBDATE ().
SELECT SUBDATE('2020-08-15', 31); // output 2020-07-15 Το παρακάτω παράδειγμα δείχνει αφαίρεση ενός διαστήματος που αναφέρεται σε ώρες / ημέρες την εβδομάδα κ.λπ.
SELECT SUBDATE('2020-01-02 12:11:11', INTERVAL 1 WEEK); // output 2019-12-26 12:11:11 SELECT SUBDATE('2020-01-02 12:11:11', INTERVAL 12 HOUR); // output 2020-01-02 00:11:11 δ) ΥΠΟΒΟΛΗ ()
Το SUBTIME αφαιρεί την τιμή ώρας που καθορίζεται σε «hh: mm: ss» από μια δεδομένη τιμή χρόνου ή πεδίου ώρας.
Σύνταξη:
SUBTIME(inputTimeValue/fieldName, ‘hh:mm:ss’)Παραδείγματα συνάρτησης SUBTIME ():
SELECT SUBTIME('2020-01-02 12:11:11', '02:20:05'); // output 2020-01-02 09:51:06 # 3) Μετατροπές ημερομηνιών
Η MySQL παρέχει μια ποικιλία ενσωματωμένων λειτουργιών από τη μετατροπή ημερομηνιών από τη μία μορφή στην άλλη. Ας δούμε τις πιο ευρέως χρησιμοποιούμενες λειτουργίες για τη μετατροπή ημερομηνιών.
α) CONVERT_TZ ()
Αυτή η λειτουργία χρησιμοποιείται για τη μετατροπή της ημερομηνίας από τη μία ζώνη ώρας σε άλλη. Αυτά είναι βολικά σε περιπτώσεις όπου υποθέστε ότι τα δεδομένα σας αποθηκεύτηκαν χρησιμοποιώντας ζώνη ώρας UTC και ενώ εμφανίζονταν θέλετε να μετατρέψετε σε ζώνη ώρας της επιλογής σας.
Για παράδειγμα, μετατροπή UTC σε MET (Ώρα Μέσης Ευρώπης).
SELECT CONVERT_TZ('2004-01-01 12:00:00', 'UTC', 'MET');Συμβουλές / Σημειώσεις
# 1) Για να λάβετε τη λίστα όλων των ζωνών ώρας στη βάση δεδομένων του συστήματος MySQL, μπορείτε να εκτελέσετε το παρακάτω ερώτημα και να χρησιμοποιήσετε οποιοδήποτε από τα ονόματα της ζώνης ώρας για μετατροπή από τη μία ζώνη ώρας στην άλλη.
select * from mysql.time_zone_name#δύο) Οι πληροφορίες ζώνης ώρας συμπληρώνονται κατά την εγκατάσταση της παρουσίας διακομιστή MySQL. Εάν το αποτέλεσμα του παραπάνω ερωτήματος είναι NULL, τότε ίσως χρειαστεί πρώτα να συμπληρώσετε τιμές ζώνης ώρας στους πίνακες συστήματος MySQL.
>> Ακολουθήστε αυτό Σύνδεσμος για περισσότερη βοήθεια σε αυτό.
β) FROM_UNIXTIME ()
Αυτή η συνάρτηση χρησιμοποιείται για τη μετατροπή μιας δεδομένης ώρας UNIX σε μορφή ώρας MySQL.
SELECT FROM_UNIXTIME(1547430881); //output 2019-01-14 07:24:41 γ) UNIX_TIMESTAMP
Αυτή η λειτουργία χρησιμοποιείται για τη μετατροπή μιας δεδομένης ώρας χρόνου MySQL σε χρονική σήμανση UNIX.
SELECT UNIX_TIMESTAMP('2020-03-15 07:10:56.123') //output 1584236456.123 Η χρονική σήμανση UNIX είναι η αναπαράσταση μιας δεδομένης ημερομηνίας με τη μορφή δευτερολέπτων που έχουν παρέλθει από την 1η Ιανουαρίου 1970, UTC.
>> Ανατρέξτε εδώ για περισσότερες λεπτομέρειες σχετικά με τη χρονική σήμανση UNIX.
# 4) Λήψη συγκεκριμένων τμημάτων στηλών DateTime
Κατά καιρούς, είναι επιθυμητό να ληφθεί ένα συγκεκριμένο μέρος της τιμής ημερομηνίας για μια τιμή πεδίου ώρας στον πίνακα. Για παράδειγμα , ας υποθέσουμε ότι θέλετε να μετρήσετε το όχι. παραγγελιών και ομαδοποίηση ανά ημέρα, εβδομάδα, μήνα κ.λπ.
Και, γενικά, αυτές οι τιμές δεν αποθηκεύονται ως ξεχωριστές στήλες στον πίνακα - για παράδειγμα, θα έχετε μόνο μία στήλη όπως - create_on το οποίο θα ήταν πεδίο ημερομηνίας και χωρίς ξεχωριστές στήλες όπως - ημερομηνία, μήνας, έτος κ.λπ.
Η MySQL παρέχει πολλές χρήσιμες μεθόδους όπου μπορείτε να εξαγάγετε το επιθυμητό μέρος από το πεδίο DateTime.
Ας υποθέσουμε ότι έχουμε μια τιμή χρόνου ως - «2020-03-15 07: 10: 56.123»
Ας ρίξουμε μια ματιά σε μερικές από τις ευρέως χρησιμοποιούμενες συναρτήσεις με παραδείγματα χρησιμοποιώντας την παραπάνω τιμή χρόνου.
α) Απόσπασμα DAY
Η MySQL παρέχει τις ακόλουθες λειτουργίες για την εξαγωγή πληροφοριών DAY:
ΗΜΕΡΟΜΗΝΙΑ() - Εξαγάγετε το μέρος DATE () του πεδίου DateTime.
το καλύτερο πρόγραμμα λήψης μουσικής για Android
SELECT DATE('2020-03-15 07:10:56.123'); //output 2020-03-15 DAYOFMONTH () - Εξαγάγετε το μέρος DAY της ώρας δεδομένων. Είναι ουσιαστικά η ημέρα του μήνα. Συντομογραφία για τη χρήση αυτής της λειτουργίας είναι DAY ()
SELECT DAYOFMONTH('2020-03-15 07:10:56.123'); //output 15 ΜΕΡΑ ΤΗΣ ΕΒΔΟΜΑΔΑΣ () - Εξάγει τον δείκτη που αντιστοιχεί στην ημέρα της εβδομάδας - τιμές μεταξύ 1 και 7.
SELECT DAYOFWEEK('2020-03-15 07:10:56.123'); //output 1 DAYOFYEAR () - Αποσπά την ημέρα με όρους όχι. ημερών πέρασαν σε ένα χρόνο. Οι τιμές κυμαίνονται μεταξύ 1 και 365 (ή 366 εάν είναι έτος άλματος).
SELECT DAYOFYEAR('2020-03-15 07:10:56.123'); //output 75 DAYNAME () - Εξάγει το όνομα της ημέρας με τιμές που κυμαίνονται από Δευτέρα έως Κυριακή.
SELECT DAYNAME('2020-03-15 07:10:56.123'); //output Sunday β) Εξαγωγή TIME
Η MySQL παρέχει τις ακόλουθες λειτουργίες για την εξαγωγή τιμών TIME:
ΧΡΟΝΟΣ() - Εξάγει το τμήμα χρόνου σε μορφή hh: mm: ss (με συνιστώσα κλασματικών δευτερολέπτων, εάν υπάρχει).
SELECT TIME('2020-03-15 07:10:56.123'); //output '07:10:56.123' ΩΡΑ () - Εξάγει το τμήμα ώρας του πεδίου / τιμή - τιμές που κυμαίνονται μεταξύ 1-24.
SELECT HOUR('2020-03-15 07:10:56.123'); //output 7 ΛΕΠΤΟ () - Εξάγει το λεπτό μέρος της δεδομένης ώρας - τιμές που κυμαίνονται μεταξύ 1-60.
SELECT MINUTE('2020-03-15 07:10:56.123'); //output 10 ΔΕΥΤΕΡΗ () - Εξάγει το δευτερόλεπτο μέρος της δεδομένης ώρας - τιμές που κυμαίνονται μεταξύ 1-60.
SELECT SECOND('2020-03-15 07:10:56.123'); //output 56 MICROSECOND () - Εξάγει το μέρος των μικροδευτερολέπτων του δεδομένου DateTime.
SELECT MICROSECOND('2020-03-15 07:10:56.123'); //output 123000 γ) Εκχύλισμα ΜΗΝΑ
ΜΗΝΑ () - Εξάγει τον δείκτη μήνα. Τιμές που κυμαίνονται μεταξύ 1 - 12.
SELECT MONTH('2020-03-15 07:10:56.123'); //output 3 MONTHNAME () - Εξάγει το όνομα του μήνα. Τιμές που κυμαίνονται μεταξύ Ιανουαρίου και Δεκεμβρίου.
SELECT MONTHNAME('2020-03-15 07:10:56.123'); //output March δ) Εξαγωγή ΕΒΔΟΜΑΔΑΣ, ΕΤΟΣ, και ΤΡΙΜΗΝΙΑ
ΕΒΔΟΜΑΔΑ() - Εξάγει την ΕΒΔΟΜΑΔΑ του έτους για τη δεδομένη τιμή DateTime.
SELECT WEEK('2020-03-15 07:10:56.123'); //output 11 ΕΤΟΣ () - Εξάγει το μέρος ΕΤΟΣ της ημερομηνίας.
SELECT YEAR('2020-03-15 07:10:56.123'); //output 2020 ΤΕΤΑΡΤΟ() - Εξάγει το τρίμηνο σε σχέση με το έτος. Τιμές που κυμαίνονται από 1 έως 4.
SELECT QUARTER('2020-03-15 07:10:56.123'); //output 1 Όλες αυτές οι παραπάνω μέθοδοι χρησιμοποιούνται ως επί το πλείστον για την ανάλυση δεδομένων και για την πραγματοποίηση προβλέψεων - για παράδειγμα, ποιες ημέρες του μήνα ήταν το όχι. των υψηλότερων παραγγελιών κ.λπ.
# 5) Εύρεση διαφοράς μεταξύ 2 ημερομηνιών
Για να βρείτε τη διαφορά μεταξύ 2 δεδομένων τιμών πεδίου δεδομένης ώρας, η MySQL παρέχει συναρτήσεις DATEDIFF () & TIMEDIFF ().
α) DATEDIFF ()
Επιστρέφει τη διαφορά μεταξύ των τιμών 2 DateTime (ή ημερομηνία) σε ημέρες.
Σύνταξη:
DATEDIFF(DateTime1, DateTIme2)Ας δούμε μερικά παραδείγματα για τη συνάρτηση DATEDIFF ():
Το DATEDIFF () χρησιμοποιεί εισόδους DateTime.
SELECT DATEDIFF('2020-01-05 12:11:11.11', '2020-01-03 14:11:11') //output 2 DATEDIFF () χρησιμοποιώντας εισόδους ημερομηνίας.
SELECT DATEDIFF('2020-04-02', '2020-01-04') //output 89 DATEDIFF () χρησιμοποιώντας εισόδους ημερομηνίας με αρνητική έξοδο.
SELECT DATEDIFF('2020-04-02', '2020-04-05') //output -3 β) TIMEDIFF ()
Αυτή η συνάρτηση επιστρέφει τη διαφορά μεταξύ 2 τιμών ώρας (ή ώρας) που εκφράζονται σε «hh: mm: ss» με ένα προαιρετικό κλασματικό μέρος ανάλογα με την είσοδο με την οποία κλήθηκε η συνάρτηση TIMEDIFF ().
Σύνταξη:
TIMEDIFF(DateTime1, DateTIme2)Σύμφωνα με την παραπάνω σύνταξη, το αποτέλεσμα της συνάρτησης TIMEDIFF () θα καθοριζόταν ως χρόνος «hh: mm: ss» για έκφραση => Ημερομηνία Ώρα - ΗμερομηνίαTIme2
Παρακαλούμε να σημειώσετε ότι,
- Τόσο η έκφραση 1 όσο και η έκφραση2 είναι του ίδιου τύπου - δηλαδή μια ώρα, άλλη μια φορά, θα επέστρεφε μια τιμή NULL.
- Το Expression1 και το Expression2 δεν μπορούν να είναι μόνο ημερομηνίες. Παράδειγμα: Το DATEDIFF (2020-01-01, 2020-02-01) θα επέστρεφε NULL.
Ας δούμε μερικά παραδείγματα για τη συνάρτηση TIMEDIFF ():
TIMEDIFF () χρησιμοποιώντας εισόδους DateTime.
SELECT TIMEDIFF('2020-01-4 12:11:11.11', '2020-01-03 12:11:11') //output 24:00:00.11 TIMEDIFF () χρησιμοποιώντας εισόδους ώρας.
SELECT TIMEDIFF('12:11:11.11', '02:12:11') //output 09:59:00.11 TIMEDIFF () επιστρέφει αρνητική έξοδο.
SELECT TIMEDIFF('2020-01-01 12:11:11.11', '2020-01-03 12:11:11') //output -47:59:59.89 Παρακαλούμε να σημειώσετε ότι Τόσο το DATEDIFF () όσο και το TIMEDIFF () μπορούν να επιστρέψουν ΑΡΝΗΤΙΚΕΣ τιμές κάθε φορά που το πρώτο όρισμα είναι μικρότερο από το δεύτερο - δηλαδή το πρώτο όρισμα είναι μια παλαιότερη τιμή dateTime σε σύγκριση με τη δεύτερη.
# 6) Μορφοποίηση ημερομηνιών
Η συνάρτηση DATE_FORMAT () μετατρέπει την ημερομηνία στη δεδομένη μορφή.
Για παράδειγμα: Μετατροπή της απαιτούμενης ημερομηνίας σε μορφή mm / yyyy.
SELECT DATE_FORMAT('2020-03-15 07:10:56.123', '%m/%Y'); //output 03/2020 Ας δούμε μερικές κοινές συμβάσεις για τη χρήση προσδιοριστών μορφής:
| Μορφή | Περιγραφή |
|---|---|
| % Υ | Τιμή έτους - 4 ψηφία |
| % Υ | Τιμή έτους - 2 ψηφία |
| %Μ | Όνομα μήνα όπως - Ιανουάριος, Φεβρουάριος |
| %Μ | Όνομα μήνα - αριθμητικό |
| %ρε | Ημέρα του μήνα |
| % ω | Ώρα - 00 - 12 |
| %Εγώ | Λεπτά |
| %ΜΙΚΡΟ | Δευτερόλεπτα |
Ανάλογα με την απαίτηση, αυτοί οι προσδιοριστές μορφής μπορούν να χρησιμοποιηθούν και το MySQL DateTime μπορεί να μετατραπεί σε απαιτούμενη μορφή.
Παράδειγμα: Η μετατροπή σε μορφή dd / mm / yyyy μπορεί να οριστεί ως:
SELECT DATE_FORMAT('2020-03-15 07:10:56.123', '%d/%m/%Y'); //output 15/03/2020 >> Ανατρέξτε εδώ για μια πλήρη λίστα προσδιοριστών μορφής που υποστηρίζεται από τη MySQL.
Συχνές Ερωτήσεις
Ε # 1) Πώς μπορούμε να αλλάξουμε το μέρος του έτους της αποθηκευμένης ημερομηνίας στη MySQL;
Απάντηση: Για να ενημερώσετε την τιμή του έτους σε έναν συγκεκριμένο αριθμό για μια δεδομένη ημερομηνία, μπορούμε να χρησιμοποιήσουμε το DATE_ADD λειτουργία και προσθέστε τη διαφορά μεταξύ του τρέχοντος έτους της ημερομηνίας και αυτού που θέλουμε να αλλάξουμε.
Για παράδειγμα: Ας υποθέσουμε ότι έχουμε την ημερομηνία «2020-03-15 07: 10: 56.123» και θέλουμε απλώς να αλλάξουμε το μερίδιο έτους της ημερομηνίας σε 2018. Για να το κάνουμε αυτό,
- Μπορούμε πρώτα να βρούμε τη διαφορά μεταξύ του έτους που πρόκειται να ενημερωθεί και του πραγματικού έτους της ημερομηνίας. Αυτό θα μπορούσε να επιτευχθεί χρησιμοποιώντας τη συνάρτηση YEAR.
SELECT (2018 - year('2020-03-15 07:10:56.123')) //output -2 - Τώρα, μπορούμε να προσθέσουμε αυτό το -2 ως χρονικό διάστημα χρησιμοποιώντας τη συνάρτηση DATE_ADD.
SELECT DATE_ADD('2020-03-15 07:10:56.123', INTERVAL -2 year) //output 2018-03-15 07:10:56.123000 Μπορείτε να δείτε παραπάνω ότι μόλις αλλάξαμε το έτος μέρος της ημερομηνίας.
Το ίδιο σύνολο βημάτων μπορεί να χρησιμοποιηθεί όταν υπάρχει απαίτηση για αλλαγή ενός συγκεκριμένου μέρους της ημερομηνίας. Παράδειγμα: Μήνας, ημέρα κ.λπ.
Ε # 2) Πώς να αλλάξετε την ώρα δεδομένων στην απαιτούμενη μορφή στο MySQL;
πώς να ανοίξετε δυαδικό αρχείο στα παράθυρα
Απάντηση: Η MySQL παρέχει συνάρτηση DATE_FORMAT () για τη μετατροπή της ώρας δεδομένων στην απαιτούμενη μορφοποιημένη συμβολοσειρά για εμφάνιση στα αποτελέσματα των ερωτημάτων.
>> Ανατρέξτε εδώ για μια πλήρη λίστα προσδιοριστών μορφής.
Παράδειγμα: Για τη μετατροπή της ώρας δεδομένων σε μορφή - dd: mm: yyyy, μπορείτε να χρησιμοποιήσετε το ακόλουθο ερώτημα:
SELECT DATE_FORMAT('2020-03-15 07:10:56.123', '%d:%m:%Y');Q # 3) Ποια είναι η μορφή ημερομηνίας στο MySQL;
Απάντηση: Η τυπική μορφή ημερομηνίας στη MySQL είναι «dd-mm-yyyy hh: mm: ss. { fractionalPart }
Δείγμα τιμής ώρας αποθηκευμένο στο MySQL - «2020-03-15 07: 10: 56.123»
Q # 4) Πώς να δημιουργήσετε έναν πίνακα με τύπο δεδομένων DATE στη MySQL.
Απάντηση: Η MySQL παρέχει διαφορετικούς τύπους δεδομένων για την αποθήκευση τιμών ημερομηνίας και ώρας ανάλογα με τις ανάγκες. Για απλώς αποθήκευση ημερομηνιών - μπορεί να χρησιμοποιηθεί ο τύπος δεδομένων DATE. Αποθηκεύει δεδομένα σε μορφή yyyy-mm-dd.
Ας δούμε το παράδειγμα καθορισμού της στήλης ως τύπου DATE:
CREATE TABLE date_illustration(date_col DATE);συμπέρασμα
Σε αυτό το σεμινάριο, μάθαμε για τους διαφορετικούς τύπους δεδομένων που παρέχονται από τη MySQL για την αποθήκευση τιμών ημερομηνίας και ώρας και πώς μπορούν να χρησιμοποιηθούν διάφορες εύχρηστες λειτουργίες που παρέχονται από το MySQL για την εκτέλεση υπολογισμών σε τιμές ημερομηνίας ώρας.
Μάθαμε επίσης για διάφορες λειτουργίες όπως η εξαγωγή συγκεκριμένων τμημάτων της δεδομένης τιμής DateTime, η μετατροπή μιας δεδομένης ημερομηνίας σε άλλη φόρμα, η προσθήκη τιμών ημερομηνίας / ώρας σε υπάρχουσες τιμές κ.λπ.
Το DateTime είναι ένας από τους πιο σημαντικούς τύπους δεδομένων στη MySQL και έχει μεγάλη σημασία για την ανάκτηση λεπτομερειών όταν συνέβη ένα συμβάν.
Αυτές οι τιμές χρησιμοποιούνται ευρέως από αναλυτές δεδομένων / μοντέλα μηχανικής μάθησης / τιμολόγησης κ.λπ., όπου γίνεται πολλή κοπή και χρονολόγηση ημερομηνιών για την προετοιμασία μοντέλων μηχανικής μάθησης και τη λήψη κρίσιμων επιχειρηματικών αποφάσεων.
Συνιστώμενη ανάγνωση
- C # DateTime Tutorial: Εργασία με ημερομηνία & ώρα σε C # με παράδειγμα
- Μορφή χρόνου SQL PL: Λειτουργίες ημερομηνίας και ώρας στο PL / SQL
- Εκμάθηση Python DateTime με παραδείγματα
- Λειτουργίες ημερομηνίας και ώρας σε C ++ με παραδείγματα
- MySQL Δημιουργία εκπαιδευτικού προγράμματος με παραδείγματα κώδικα
- Εισαγωγή MySQL σε πίνακα - Εισαγωγή σύνταξης δήλωσης και παραδείγματα
- MySQL Δημιουργία εκπαιδευτικού πίνακα με παραδείγματα
- MySQL Delete Statement - Διαγραφή σύνταξης εντολών και παραδείγματα