MySQL datetime formats and displaying different ways

[sdowney1]

Was wondering about current_timestamp in a datetime column

Do you change the format of how datetime is inserted, or alter it while being read to look differently?

If you do nothing, does windows interpret the datetime to the default view of the os settings?

Cause I insert current_timestamp and read it goes in like YYYY MM DD HH mm ss or such
but in a list view it displays as

12/22/2015 11:15:32 AM etc...

Without me doing any date time formatting, AFAIK,

[sdowney1]

must be defaults to how windows setup to display date time information.

I just switched from datetime column to a timestamp column in same table and date time displays in listview exactly the same.

[sapator]

Timestamp is timestamp, it's a binary value like 0x0000000003C78ECB so I'm not sure how you see a date.

https://stackoverflow.com/questions/...p-in-sqlserver

[techgnome]

Don't confuse the value with the display....
Consider that the number 123456789 is the same value, whether it's displayed as 123456789 or 123,456,789... Pull it from the DB as a datetime, then format it when you display it.

-tg

[sdowney1]

I have not had to do anything special like formatting anything

TS or Datetime, both after loaded into listview show date and time .tostring(), with nothing else needed

'cmd1.CommandText = "Select unID, unDatetime, unTitleDuplicate, unBarcode, unMystatus From unbookdata order by unDateTime DESC"
'cmd1.CommandText = "Select unID, unDatetime, unTitleDuplicate, unBarcode, unMystatus From unbookdata order by id DESC"
cmd1.CommandText = "Select unID, TS, unTitleDuplicate, unBarcode, unMystatus From unbookdata order by id DESC"

Using RDR = cmd1.ExecuteReader()

	Do While RDR.Read()
		Dim LItem As New ListViewItem()
		LItem.Text = RDR("unID").ToString() '0
		LItem.SubItems.Add(RDR("unBarcode").ToString()) '1
		'LItem.SubItems.Add(RDR("unDateTime").ToString())                  '"yyyy-MM-dd HH:mm:ss")) '2
		LItem.SubItems.Add(RDR("TS").ToString())                                '"yyyy-MM-dd HH:mm:ss")) '2
		LItem.SubItems.Add(RDR("unTitleDuplicate").ToString()) '3
		LItem.SubItems.Add(RDR("unMystatus").ToString()) '4
		ListView1.Items.Add(LItem)
	Loop

End Using

unbookdata explained, and I should change the default for undatetime to match TS's defaults.

Current database: booksgood

+------------------+---------------+------+-----+---------------------+-------------------------------+
| Field            | Type          | Null | Key | Default             | Extra                         |
+------------------+---------------+------+-----+---------------------+-------------------------------+
| Id               | int(11)       | NO   | PRI | NULL                | auto_increment                |
| unId             | int(11)       | YES  |     | 0                   |                               |
| unTitles         | text          | YES  |     | NULL                |                               |
| unGeneralNote    | text          | YES  |     | NULL                |                               |
| unAuthor         | varchar(100)  | YES  |     |                     |                               |
| unImprint        | varchar(100)  | YES  |     |                     |                               |
| unISBN           | varchar(100)  | YES  |     |                     |                               |
| unDescription    | varchar(100)  | YES  |     |                     |                               |
| unCallNumberPre  | varchar(5)    | YES  |     |                     |                               |
| unCallNumber     | varchar(25)   | YES  |     |                     |                               |
| unAccession      | varchar(25)   | YES  |     |                     |                               |
| unBibliography   | varchar(100)  | YES  |     |                     |                               |
| unSeries         | varchar(100)  | YES  |     |                     |                               |
| unMystatus       | varchar(70)   | YES  |     |                     |                               |
| unBarcode        | varchar(50)   | YES  |     |                     |                               |
| unLocalData      | varchar(100)  | YES  |     |                     |                               |
| unCheckoutPeriod | varchar(10)   | YES  |     |                     |                               |
| unCatalogCard    | text          | YES  |     | NULL                |                               |
| unSummary        | text          | YES  |     | NULL                |                               |
| unMyCount        | varchar(10)   | YES  |     |                     |                               |
| unDateTime       | datetime      | YES  |     | NULL                |                               |
| unMyUser         | varchar(50)   | YES  |     |                     |                               |
| unMarcData       | text          | YES  |     | NULL                |                               |
| unSdlsRecord     | text          | YES  |     | NULL                |                               |
| TS               | timestamp     | NO   |     | current_timestamp() | on update current_timestamp() |
| unEdits          | char(1)       | YES  |     |                     |                               |
| unTitleDuplicate | varchar(50)   | YES  | MUL |                     |                               |
| unLOCNumber      | varchar(100)  | YES  |     | NULL                |                               |
| unLOSC           | varchar(5)    | YES  |     |                     |                               |
| unLOSN           | decimal(14,6) | YES  |     | 0.000000            |                               |
+------------------+---------------+------+-----+---------------------+-------------------------------+
30 rows in set (0.053 sec)

MariaDB [booksgood]>

including a snip of the appearance

[sdowney1]

This makes me wonder why format any timestamp or datetime column outputs, if windows will just display by default as it is showing.

Just not needed.

[sdowney1]

That pic in post 5 is using the timestamp column TS

cmd1.CommandText = "Select unID, TS, unTitleDuplicate, unBarcode, unMystatus From unbookdata order by id DESC"

with this reader

LItem.SubItems.Add(RDR("TS").ToString())

And you can see the datetime in the listview looks great

[techgnome]

This makes me wonder why format any timestamp or datetime column outputs, if windows will just display by default as it is showing.

Just not needed.

What if you don't want the time? Then you have to suppoly the format to use. What if you want to displaay in ISO format (YYYY-MM-DD) for sorting reasons? What if you want just the time? Or just the month? If .toString() works as is, grwat... but thaat's not always the case.


-tg

[sdowney1]

What if you don't want the time? Then you have to suppoly the format to use. What if you want to displaay in ISO format (YYYY-MM-DD) for sorting reasons? What if you want just the time? Or just the month? If .toString() works as is, grwat... but thaat's not always the case.


-tg

Ok but I want the date and time. don't see a reason not to have both. they can both be relevant. This is a list of records that have been 'deleted'.

[techgnome]

As I said, if the default works... great... but that's not always going to be the case. Just because you don't need alternate formats (I's still argue it's a good thing to specify the format even if it's the same as the default) today, odds are at some point you will need or want to change the format. It's just something to keep in mind for the future - that's all.

-tg

[sdowney1]

What I find interesting is vbnet and windows know how to display a timestamp and datetime column as a date time. You don't need to format it at all.
Must display as regional date time settings, so could work anywhere in the world as your windows os displays date time, it will match.

The other question I had is, when you insert a DateTime value into Mysql datetime column, does anyone format the insert to be a certain way, or is it just formatted to look a certain way when being read.

[sdowney1]

As I said, if the default works... great... but that's not always going to be the case. Just because you don't need alternate formats (I's still argue it's a good thing to specify the format even if it's the same as the default) today, odds are at some point you will need or want to change the format. It's just something to keep in mind for the future - that's all.

-tg

ok, way i think about it, user is used to seeing a date time in their OS to look a particular way, and just letting it default to the OS settings, it will look the same way as they see all date time values. If you format the output, how would you decide what format to use for what world region, country etc.

[techgnome]

Sigh... It depends... that's the only reaason I bring this up... to show/demonstrate that there are multiple ways... and depending on your needs, you may or may not format. You've stated more than once that the current default is fine by you. And more than once I've said that's fine. I just want to point out that the default isn't always wanted. In my case I have restricitons on how dates are displayed and the main requirement is that dates are almost always (like 9.5/10 times) in DDDD MMMM dd, YYYY format ... as in "Thursday July 18, 2024" ... default formatting isn't going to cut it there. That's all I'm saying. There will come a time when you want something other than the default.

As for the db.,.. how's displayed in the db is irrelevant... but yes, I insert using the ISO YYYY-MM-DD hh:mm:ss format ... because I don't want Jan 5 and May 1 getting confused with each other... but then if it's a date from start to finish, then it's less of a problem. But I've dealt with enough international data and the day-month-year vs month-day-year problem that I just use a non-ambiguous format -- locale settings be damned because the user is in London, but the server is American based in Chicago... who is to say which format is correct?


-tg

[sdowney1]

https://stackoverflow.com/questions/...y-select-query

I have not tried any of these examples, you can configure datetime displays right in the query for the DB

No need to format using vbnet?