Datetime - Revision history

Bleep bloop: Bot: Orphan page, add template

2017-04-17T02:39:59Z

Bot: Orphan page, add template

← Older revision		Revision as of 02:39, 17 April 2017
Line 1:		Line 1:
			{{Orphan\|date=April 2017}}
	Dealing with date and time stamps in CSV files for populating an SQL database, all using Python.		Dealing with date and time stamps in CSV files for populating an SQL database, all using Python.

Admin: /* Examples */

2016-02-05T16:52:03Z

Examples

← Older revision		Revision as of 16:52, 5 February 2016
Line 149:		Line 149:
	</source>		</source>
	-->		-->

			==Refs==

			http://www.w3schools.com/sql/sql_dates.asp

			https://www.sqlite.org/lang_datefunc.html

Admin: /* Examples */

2016-02-05T16:50:56Z

Examples

← Older revision		Revision as of 16:50, 5 February 2016
Line 116:		Line 116:
	Compute the date and time given a unix timestamp 1092941466, and compensate for your local timezone.		Compute the date and time given a unix timestamp 1092941466, and compensate for your local timezone.
	</source>		</source>

			<!--

	Compute the current unix timestamp.		Compute the current unix timestamp.
Line 146:		Line 148:
	SELECT (julianday('now') - 2440587.5)*86400.0;		SELECT (julianday('now') - 2440587.5)*86400.0;
	</source>		</source>
			-->

Admin: /* Examples */

2016-02-05T16:49:32Z

Examples

@@ Line 106: / Line 106: @@
 Compute the date and time given a unix timestamp 1092941466.
 SELECT datetime(1092941466, 'unixepoch');
-Compute last day of current month:
+<source lang="sql">
-<pre>
+<source lang="sql">
-SELECT date('now','start of month','+1 month','-1 day');
+SELECT (julianday('now') - 2440587.5)*86400.0;
-</pre>
+</source>

Admin: /* Dates/Times in Sqlite */

2016-02-05T16:47:18Z

Dates/Times in Sqlite

← Older revision		Revision as of 16:47, 5 February 2016
Line 40:		Line 40:

	The Second Question: How does SQL deal with date and time stamps? How do you initialize a table with a column for date and time stamps, and how do you create a new record with that information?		The Second Question: How does SQL deal with date and time stamps? How do you initialize a table with a column for date and time stamps, and how do you create a new record with that information?

			==Data Storage in Sqlite==

			All data stored by Sqlite has a storage class of null, integer, real (float), text, or blob.

			Sqlite does not have a separate boolean storage class, booleans are stored as integer 0 (false) or integer 1 (true)

			Sqlite does not have a storage class aside for storing dates and/or times. Built-in date and time functions of sqlite are capable of storing dates and times as TEXT, REAL, or INTEGER values

			For example, I can store the date and time as a string "YYYY-MM-DD HH:MM:SS.SSS"

			I can also store it as a real number: number of days since noon in Greenwich on November 24, 4714 BC, according to proleptic Gregorian calendar.

			I can store it as an integer, using a Unix timestamp, which is the number of seconds since 1970-01-01 00:00:00 UTC.

	==Dates/Times in Sqlite==		==Dates/Times in Sqlite==

Admin: /* Examples */

2016-02-05T16:44:30Z

Examples

← Older revision		Revision as of 16:44, 5 February 2016
Line 83:		Line 83:

	==Examples==		==Examples==

			Compute the current date:

	<source lang="sql">		<source lang="sql">
	SELECT date('now');		SELECT date('now');
	</source>		</source>

			Compute the date and time given a unix timestamp 1092941466.

			SELECT datetime(1092941466, 'unixepoch');


			Compute last day of current month:

			<pre>
			SELECT date('now','start of month','+1 month','-1 day');
			</pre>

Admin: /* The Second Question: SQL and Date/Time Stamps */

2016-02-05T16:43:24Z

The Second Question: SQL and Date/Time Stamps

← Older revision		Revision as of 16:43, 5 February 2016
Line 40:		Line 40:

	The Second Question: How does SQL deal with date and time stamps? How do you initialize a table with a column for date and time stamps, and how do you create a new record with that information?		The Second Question: How does SQL deal with date and time stamps? How do you initialize a table with a column for date and time stamps, and how do you create a new record with that information?

			==Dates/Times in Sqlite==

			SQLite documentation: https://www.sqlite.org/lang_datefunc.html

			SQLite supports 5 date and time functions (date(), time(), datetime(), julianday(), strftime())

			The date and time functions use the ISO-8601 standard for their date and time formats. Dates are returned as YYYY-MM-DD and times are returned as HH:MM:SS.

			The datetime function returns YYYY-MM-DD HH:MM:SS

			Julian day returns number of days since noon in Greenwich on November 24, 4714 BC.

			strftime() routine returns date formateed according to format string specified as first argument. This is a string with any of the following characters:

			<pre>
			%d day of month: 00
			%f fractional seconds: SS.SSS
			%H hour: 00-24
			%j day of year: 001-366
			%J Julian day number
			%m month: 01-12
			%M minute: 00-59
			%s seconds since 1970-01-01
			%S seconds: 00-59
			%w day of week 0-6 with Sunday==0
			%W week of year: 00-53
			%Y year: 0000-9999
			%% %
			</pre>

			Also, the output of each function can be reproduced by strftime():

			<pre>
			date(...) strftime('%Y-%m-%d', ...)
			time(...) strftime('%H:%M:%S', ...)
			datetime(...) strftime('%Y-%m-%d %H:%M:%S', ...)
			julianday(...) strftime('%J', ...)
			</pre>

			Modifiers to the time string can alter date and time too.

			==Examples==

			<source lang="sql">
			SELECT date('now');
			</source>

Admin: /* Dates and Times in Python */

2016-02-05T06:21:07Z

Dates and Times in Python

← Older revision		Revision as of 06:21, 5 February 2016
Line 27:		Line 27:
	</source>		</source>

	~~Now, if~~ we print out ~~the information in those variables~~:		If we print out x_datetime, we can see it is a datetime object:

	<pre>		<pre>

Admin: /* Dates and Times in Python */

2016-02-05T06:13:47Z

Dates and Times in Python

← Older revision		Revision as of 06:13, 5 February 2016
Line 16:		Line 16:

	We can call it like so:		We can call it like so:

			'''test_datetime.py'''

	<source lang="python">		<source lang="python">

Admin: /* The First Question: Dealing With CSV Date/Time Stamps */

2016-02-05T06:13:14Z

The First Question: Dealing With CSV Date/Time Stamps

@@ Line 4: / Line 4: @@
 The First Question: How to deal with date and time stamps from CSV files, and their many varieties?
 =The Second Question: SQL and Date/Time Stamps=
 The Second Question: How does SQL deal with date and time stamps? How do you initialize a table with a column for date and time stamps, and how do you create a new record with that information?