I was writing a backend program with rails + mysql, trying to retrive a "date" type column to my frontend.
sql = <<-SQL
SELECT ...............,
DATE_FORMAT(sd.date_agreed,'%Y-%m-%d') as date_agreed,
DATE_FORMAT(sd.date_agreed,'%Y-%m-%d') as date_agrd,
..........
FROM shipping_demurrages sd
WHERE ......
SQLbasically The "date_agreed" and "date_agrd" should be exactly the same, but my front end received different data:
date_agrd: "2022-12-20"
date_agreed: "2022-12-20 00:00:00 -0600"actually, any other name except "date_agreed" will give a date result such as "2022-12-20", but somehow if I give "date_agreed" as the name, the result will be date+time my question will be :
- why different alias name will cause different result?
- the date_agreed column is already a date type, but if I dont do the DATE_FORMAT thing, my frontend will always receive a datetime such as "2022-12-20 00:00:00 -0600" but not a date only as I expected. Why is that?
BTW, my backend is Rails 4.2.6, mysql Ver 15.1 Distrib 5.5.33-MariaDB, for Linux (x86_64) using readline 5.1
my front-end is EXTJS 6.2 running in chrome browser. I use chrome developer tools to monitor the data sent from backend.
1条答案
按热度按时间1u4esq0p1#
the date_agreed column is already a date type, but if I dont do the DATE_FORMAT thing, my frontend will always receive a datetime such as "2022-12-20 00:00:00 -0600" but not a date only as I expected. Why is that?
Because you're doing it wrong.
If you're writing an API that serves your frontend you shouldn't rely on implicitly casting dates or times into strings. Instead you should EXPLICITY output the dates in a standardized format such as ISO8601. Otherwise your application is bound to break when you update it.
This removes the whole need to perform the wonky database query. And I'm guessing the issue isn't so much the alias but you might have a column with the same name defined which is causing ActiveRecord to cast the values into a DateTime which is then being dumped -
-6 his most likely due to your timezone settings.You can do this by adding a serialization layer such as ActiveModel::Serializers or jBuilder or by creating a decorator.