49

I am guessing this is relatively simple to do, but I am unsure of the syntax. I have date and time columns that I want to combine to a timestamp column. how would I query this using a select?

shgnInc
  • 2,054
  • 1
  • 23
  • 34
Richard
  • 15,152
  • 31
  • 85
  • 111

7 Answers7

98

Or you could use the built-in TIMESTAMP(date,time) function.

So then you would do something like this say from an Orders table...

SELECT OrderNumber, TIMESTAMP(date,time) as OrderTS, SalesPersonID
FROM Orders
ConceptRat
  • 1,079
  • 7
  • 4
  • 2
    This heavily upvoted answer looks more like a comment. If you intend to keep this post as an answer, please take the time to beef it up so that it is not merely a link-only answer. – mickmackusa Apr 27 '18 at 02:04
60

Mysql does not seem to have a constructor for datetime such as datetime('2017-10-26', '09:28:00'). So you will have to treat the component part as string and use string concatenation function (Note mysql does not have the || operator for string concatenation). If you want the datetime type, you will have to cast it.

concat(datefield,' ',timefield) as date

select cast(concat('2017-10-26', ' ', '09:28:00') as datetime) as dt;
Kemin Zhou
  • 6,264
  • 2
  • 48
  • 56
Haim Evgi
  • 123,187
  • 45
  • 217
  • 223
  • I needed to use it in a constraint with todays date, this did the trick `select * from my_table where my_date = cast(concat(CURDATE(), ' ', '13:00:00') as datetime)` – radtek Jun 11 '18 at 15:50
8

If it possible to use built-in function, just use it. Any way here is an example to find records between given timestamps.

SELECT `id` FROM `ar_time` WHERE TIMESTAMP(`cdate`,`ctime`) BETWEEN fromTimeStamp AND nowTimeStamp;
Tom Lime
  • 1,154
  • 11
  • 15
4

For 24hr time

TIMESTAMP(Date, STR_TO_DATE(Time, '%h:%i %p'))
Daniel James Canil
  • 306
  • 2
  • 10
4
SELECT * FROM tablename WHERE TIMESTAMP(datecol, timecol) > '2015-01-01 12:00:00';
Lydia
  • 2,377
  • 16
  • 13
3

O.P. did say SELECT but in case anyone wants to add a timestamp column:

ALTER TABLE `t` ADD COLUMN `stamp` TIMESTAMP;
UPDATE `t` SET `stamp` = STR_TO_DATE(CONCAT(`Date`, ' ', `Time`), '%m/%d/%Y %H:%i:%s');

Adjust format strings to taste.

Community
  • 1
  • 1
Bob Stein
  • 16,271
  • 10
  • 88
  • 101
-1

concat('2021-12-31', ' ', '07:00:00')

it worked in an INSERT procedure.

i k
  • 1
  • 1
  • This does not provide an answer to the question. Once you have sufficient [reputation](https://stackoverflow.com/help/whats-reputation) you will be able to [comment on any post](https://stackoverflow.com/help/privileges/comment); instead, [provide answers that don't require clarification from the asker](https://meta.stackexchange.com/questions/214173/why-do-i-need-50-reputation-to-comment-what-can-i-do-instead). - [From Review](/review/late-answers/30570376) – Saman Salehi Dec 15 '21 at 05:48